我想在一個表格中進行總結，以便我擁有每一輪中每個id的標記“紅色”和“藍色”的數量。對于這樣的表，SQL 查詢會是什么？
這是資料。
id | round | color | ntokens
1 | 1 | blue | 5
1 | 2 | red | 83
1 | 3 | blue | 77
2 | 3 | blue | 3
2 | 3 | red | 2
3 | 1 | red | 4
3 | 1 | blue | 55
3 | 2 | blue | 22
結果將顯示如下：
id | round | blue_count | red_count
1 | 1 | 5 | 0
1 | 2 | 0 | 83
1 | 3 | 77 | 0
2 | 3 | 3 | 2
3 | 1 | 44 | 4
3 | 2 | 22 | 0
我一直在嘗試以下代碼：
SELECT id, round,
COUNT(color='red') as red_count,
COUNT(color='blue') as blue_count
FROM data
GROUP BY id, round
uj5u.com熱心網友回復：
count計算不是 的值null。=回傳trueor false，兩者都不是null。您可以使用case運算式來創建所需的行為：
SELECT id,
round,
COUNT(CASE color WHEN 'red' THEN 1 END) as red_count,
COUNT(CASE color WHEN 'blue' THEN 1 END) as blue_count
FROM data
GROUP BY id, round
uj5u.com熱心網友回復：
您必須在 COUNT 中使用 CASE。
SELECT id, round,
SUM(CASE WHEN color = 'red' THEN 1 END) as red_count,
SUM(CASE WHEN color = 'blue' THEN 1 END) as blue_count
FROM table_that_I_hope_is_not_actually_called_data
GROUP BY id, round
uj5u.com熱心網友回復：
我建議這樣做以獲得請求的結果表：
SELECT
id, round,
SUM(CASE WHEN color = 'red' THEN ntokens END) AS red_count,
SUM(CASE WHEN color = 'blue' THEN ntokens END) AS blue_count
FROM data
GROUP BY id, round
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/381365.html
標籤：sql
上一篇：在同一查詢中搜索多列