[請益] MySQL集合函數條件問題
假設有兩個table
結構如下
table a
a_id
a_label
table b
a_id
b_id
b_date
table b是用來紀錄table a的點擊率的
我想在同一個資料集拉出今天跟全部的點擊率
如果分開來下會是對的結果，但結合在一起下就會出問題
舉例來說，假設某筆資料總共有3個點擊數量
昨天1筆今天2筆
這樣會正確顯示出2筆
SELECT a.a_label, COUNT(b1.b_id) AS hitsToday
FROM a
LEFT JOIN b b1 ON b1.a_id = a.a_id AND DATEDIFF(b1.b_date, CURDATE()) = 0
GROUP BY a.a_id
這樣會正確顯示出3筆
SELECT a.a_label, COUNT(b2.b_id) AS hitsTotal
FROM a
LEFT JOIN b b2 ON b2.a_id = a.a_id
GROUP BY a.a_id
可是這樣會顯示出6筆…請問我哪邊下錯了？
SELECT a.a_label, COUNT(b1.b_id) AS hitsToday, COUNT(b2.b_id) AS hitsTotal
FROM a
LEFT JOIN b b1 ON b1.a_id = a.a_id AND DATEDIFF(b1.b_date, CURDATE()) = 0
LEFT JOIN b b2 ON b2.a_id = a.a_id
GROUP BY a.a_id
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 114.32.163.57
→
04/22 23:30, , 1F
04/22 23:30, 1F
→
04/22 23:30, , 2F
04/22 23:30, 2F
→
04/22 23:35, , 3F
04/22 23:35, 3F
→
04/22 23:46, , 4F
04/22 23:46, 4F
→
04/23 01:16, , 5F
04/23 01:16, 5F
討論串 (同標題文章)
以下文章回應了本文：
完整討論串 (本文為第 1 之 2 篇)：