http://sqlfiddle.com/#!9/1ad0f03/1

SELECT s.service_id, COUNT(*) 
FROM `appointment_services` s
LEFT JOIN  appointment a 
ON a.id = s.appointment_id
WHERE a.date BETWEEN '2018-12-10' AND '2018-12-18'
GROUP BY s.service_id

你预期结果的问题是你的错误 WHERE 子句，因此有效结果为：

service_id COUNT(*)
18          1
21          1
24          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

我建议你加入 appointment_services 上的子查询的表 appointment 对你想要的日期范围有限制。这将允许我们保留所有服务价值，即使没有匹配的预约。

SELECT
    s.service_id,
    COUNT(a.id) AS times
FROM appointment_services s
LEFT JOIN
(
    SELECT id
    FROM appointment
    WHERE date BETWEEN '2018-12-10' AND '2018-12-18'
) a
    ON s.appointment_id = a.id
GROUP BY
    s.service_id;

演示

注意，我故意更改了 service_id = 18 这样它的单个约会就不在你想要的日期范围内了。使用我建议的方法，我们仍然报告 18 计数为零。做一个笔直的内部连接会被过滤掉 18 完全，它会出现在结果集中。

必须使用help group by语句按服务id列对数据进行分组。例如：

SELECT s.service_id, 
       count(*) as times
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'
GROUP BY s.service_id

您可以在下面尝试使用count（）聚合和分组方式

SELECT s.service_id, count(*) as cnttimes
    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'
    group by s.service_id