mysql - 连接两个表后计数

Question

我有以下表格:

约会

id |    date    |   time
1  | 2018-12-02 | 10:00:00
2  | 2018-12-05 | 12:00:00
3  | 2018-12-12 | 16:00:00
4  | 2018-12-12 | 17:00:00
5  | 2018-12-13 | 09:00:00

appointment_services

id | appointment_id | service_id
1  |        1       |     24
2  |        2       |     24
3  |        3       |     21
4  |        4       |     24
5  |        5       |     18

我想从 appointment 表中搜索一个日期期间，并从 appointment_services 表中计算每个 service_id。

所以最终结果会是

service_id | times
     24    |   3
     21    |   1
     18    |   1

这是我目前所做的

SELECT * FROM `appointment` a
INNER JOIN appointment_services s ON a.id = s.appointment_id
WHERE a.date BETWEEN '2018-12-10' AND '2018-12-18'

Answer 1

你很接近:

select s.service_id, count(*) as times
from appointment_services s
join appintment a on a.id = s.appointment_id
where a.date between '2018-12-10' and '2018-12-18'
group by s.service_id