: 可是這樣會顯示出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
可以使用 sub select
select
a.a_label,
(
select count(b.b_id) from b where b.a_id=a.a_id and
DATEDIFF(b.b_date,CURDATE()) = 0 group by b.a_id
) as hitsToday,
(
select count(b.b_id) from b where b.a_id=a.a_id group by b.a_id
) as hitsTotal
from a
+---------+-----------+-----------+
| a_label | hitsToday | hitsTotal |
+---------+-----------+-----------+
| test1 | 1 | 3 |
| test2 | NULL | NULL |
+---------+-----------+-----------+
+------+---------+
| a_id | a_label |
+------+---------+
| 1 | test1 |
| 2 | test2 |
+------+---------+
+------+------+------------+
| a_id | b_id | b_date |
+------+------+------------+
| 1 | 1 | 2010-04-21 |
| 1 | 2 | 2010-04-21 |
| 1 | 3 | 2010-04-22 |
+------+------+------------+
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 111.255.44.223