我正在嘗試在 MySQL 中撰寫一個 select 陳述句，以便在獲得匹配項后獲得 5 行，然后對這些數字重復的次數求和。
例子：
原始表
| ID | 數字 |
|---|---|
| 1 | 1 |
| 2 | 0 |
| 3 | 9 |
| 4 | 14 |
| 5 | 11 |
| 6 | 0 |
| 7 | 3 |
| 8 | 4 |
| 9 | 10 |
| 10 | 9 |
| 11 | 0 |
| 12 | 5 |
| 13 | 3 |
| 14 | 11 |
| 15 | 0 |
我需要找到數字為 0 的每一行，然后在此之后選擇并顯示 5 行并計算數字的出現。如何選擇數字并獲得計數結果？
第一次選擇的結果應該是這樣的：
| ID | 數字 |
|---|---|
| 3 | 9 |
| 4 | 14 |
| 5 | 11 |
| 6 | 0 |
| 7 | 3 |
| 7 | 3 |
| 8 | 4 |
| 9 | 10 |
| 10 | 9 |
| 11 | 0 |
| 12 | 5 |
| 13 | 3 |
| 14 | 11 |
| 15 | 0 |
上次查詢中每個數字的計數結果。
| 數字 | 數數 |
|---|---|
| 9 | 2 |
| 14 | 1 |
| 11 | 2 |
| 0 | 3 |
| 3 | 3 |
| 4 | 1 |
| 10 | 1 |
| 5 | 1 |
uj5u.com熱心網友回復：
這是一個獲得預期結果的演示：
選擇號碼：
SELECT id, number
FROM (
SELECT b.id, b.number, ROW_NUMBER() OVER(PARTITION BY a.id ORDER BY b.id ASC) r
FROM numbers a
JOIN numbers b ON a.id < b.id
WHERE a.number = 0
) t WHERE t.r <= 5
ORDER BY id
數數：
WITH n AS (
SELECT id, number
FROM (
SELECT b.id, b.number, ROW_NUMBER() OVER(PARTITION BY a.id ORDER BY b.id ASC) r
FROM numbers a
JOIN numbers b ON a.id < b.id
WHERE a.number = 0
) t WHERE t.r <= 5
)
SELECT number, COUNT(*) counts
FROM n
GROUP BY number
樣本資料：
CREATE TABLE numbers (
id INT PRIMARY KEY auto_increment,
number INT NOT NULL
);
INSERT INTO numbers ( number ) VALUES (1),(0),(9),(14),(11),(0),(3),(4),(10),(9),(0),(5),(3),(11),(0);
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1fe71080cfb27680eb2a37b721e5de2d
MySQL v5.7 更新
SELECT n.*
FROM numbers n
JOIN (
SELECT a.id, SUBSTRING_INDEX(GROUP_CONCAT(b.id ORDER BY b.id SEPARATOR ','), ',', 5) selections
FROM numbers a
JOIN numbers b ON a.id < b.id
WHERE a.number = 0
GROUP BY a.id
) t ON FIND_IN_SET(n.id, t.selections)
ORDER BY n.id
SELECT n.number, COUNT(*) counts
FROM numbers n
JOIN (
SELECT a.id, SUBSTRING_INDEX(GROUP_CONCAT(b.id ORDER BY b.id SEPARATOR ','), ',', 5) selections
FROM numbers a
JOIN numbers b ON a.id < b.id
WHERE a.number = 0
GROUP BY a.id
) t ON FIND_IN_SET(n.id, t.selections)
GROUP BY n.number
ORDER BY n.number
https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=3be09acab5cd696ec4b01585eb5c32ed
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/361081.html
下一篇：帶有2個表的條件的MySQL查詢