假設有兩個table
結構如下

table a
a_id
a_label

table b
a_id
b_id
b_date

table b是用來紀錄table a的點擊率的
我想在同一個資料集拉出今天跟全部的點擊率
如果分開來下會是對的結果，但結合在一起下就會出問題
舉例來說，假設某筆資料總共有3個點擊數量
昨天一筆今天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