我有一個名為run的表，其中包含以下值。
| o_id | 開始日期 |
|---|---|
| 六 | 2021-12-30 03:16:22 |
| 六 | 2021-12-30 03:18:50 |
| 六 | 2021-12-30 05:07:18 |
| 六 | 2021-12-30 05:19:05 |
| 哦 | 2021-12-30 03:30:01 |
| 哦 | 2021-12-30 03:32:04 |
| 哦 | 2021-12-30 04:23:12 |
| 哦 | 2021-12-30 05:11:24 |
我正在嘗試為每個“o_id”獲得每小時記錄數的輸出。
這個sql查詢：
SELECT HOUR(start_date) AS HOUR,
COUNT('o_id') AS lo
FROM runs
WHERE start_date BETWEEN str_to_date('2021-12-30 02', '%Y-%m-%d %H') AND str_to_date('2021-12-31 02', '%Y-%m-%d %H')
AND o_id = 'lo'
GROUP BY HOUR(start_date)
ORDER BY HOUR(start_date);
回傳：
| 小時 | 哦 |
|---|---|
| 3 | 2 |
| 4 | 1 |
| 5 | 1 |
我想得到一個單一的查詢來回傳這樣的輸出：
| 小時 | 哦 | 六 |
|---|---|---|
| 3 | 2 | 2 |
| 4 | 1 | 0 |
| 5 | 1 | 2 |
我努力了：
SELECT HOUR(start_date) AS HOUR, COUNT('o_id') AS vi
FROM runs W
WHERE W.start_date BETWEEN str_to_date('2021-12-30 02', '%Y-%m-%d %H')
AND str_to_date('2021-12-31 02', '%Y-%m-%d %H')
AND W.o_id = 'vi'
UNION SELECT HOUR(start_date) AS HOUR, COUNT('o_id') AS lo
FROM runs W
WHERE W.start_date BETWEEN str_to_date('2021-12-30 02', '%Y-%m-%d %H')
AND str_to_date('2021-12-31 02', '%Y-%m-%d %H')
AND W.orchestrator_id = 'lo'
但我顯然誤解了這是如何作業的。我覺得我需要Left Join，但我想不出如何讓它在我的特定實體中作業。有什么建議嗎？
uj5u.com熱心網友回復：
您可以在您的COUNT():
SELECT HOUR(start_date) AS HOUR,
COUNT(case when o_id = 'lo' then 1 end) AS lo,
COUNT(case when o_id = 'vi' then 1 end) AS vi
FROM runs
WHERE start_date BETWEEN str_to_date('2021-12-30 02', '%Y-%m-%d %H') AND str_to_date('2021-12-31 02', '%Y-%m-%d %H')
GROUP BY HOUR(start_date)
ORDER BY HOUR(start_date);
小提琴
uj5u.com熱心網友回復：
一個UNION查詢解決不了你的問題，因為它會結合2個查詢行。要“組合列”，您可以使用 aJOIN但這對于您的用例來說不是必需的。
將SUM函式與 a 一起使用CASE WHEN：
SELECT HOUR(start_date) AS hour
, SUM(CASE WHEN o_id = 'lo' THEN 1 ELSE 0 END) AS lo
, SUM(CASE WHEN o_id = 'vi' THEN 1 ELSE 0 END) AS vi
FROM runs
WHERE start_date BETWEEN str_to_date('2021-12-30 02', '%Y-%m-%d %H')
AND str_to_date('2021-12-31 02', '%Y-%m-%d %H')
GROUP BY hour
ORDER BY hour;
我還替換了
GROUP BY&ORDER BY子句以重用hour別名。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/400249.html
標籤：mysql