如何在第三个表的查询中包含来自其他两个表的筛选行计数？

Question

我有一个 MySql 数据库，其中包含三个表，我需要在查询中组合这些表：计划表、注册表和候补表。 他们正在实施基础班招生制度。

时间表包含预定的课程。 当用户注册课程时，他们的用户帐户 ID 和预定课程的 ID 会存储在 enrolled 中。 如果班级已满，则会将其存储在候补名单中。 所有三个表共享一个用于标识每个类别的 ScheduleId 列。

当我查询时间表表时，我还需要返回注册列和等待列表列，这些列表示注册并等待特定 ScheduleId 的用户数量。

我为完成此任务而提出的初步查询是：

select s.id, s.classDate, s.instructor, COUNT(e.id) as enrolled
from schedule as s
left outer join enrolled as e
on s.id = e.scheduleId
group by s.id

这对于其中一个或另一个都可以正常工作，但显然我无法通过这种方式获取已注册表和候补表的值。 有人能建议一个好方法吗？

Answer 1

使用嵌套 SELECT 查询。 假设一些关于你的模式，像这样的东西怎么样（可能不适用于某些 SQL 风格）：

select s.id, s.classDate, s.instructor, 
       (select COUNT(e.id) from enrolled e where e.scheduleId = s.id) as enrolled,
       (select COUNT(w.id) from waitlist w where w.scheduleId = s.id) as waiting
from schedule as s
group by s.id

Answer 2

我会用另一个左连接和两个内联计数（不同）来实现

select s.id, s.classDate, s.instructor ,
count(distinct e.id) as enrolled,
count(distinct w.id) as waiting
from schedule as s
left outer join enrolled as e
on s.id = e.scheduleID
left outer join waitlist as w
on s.id = w.scheduleID
group by s.id

当我运行这种方法与子查询相比时，它的执行速度大约是其两倍，但我正在查看一个非常小的结果集。

Answer 3

两种快速方法：

1- 使用 COUNT(DISTINCT e.id), COUNT(DISTINCT w.id) 获取每个表中唯一实例的数量，然后连接两个表。 这可能是极其低效的。

2-在FROM子句中使用子查询（仅适用于MySQL 5.0及更高版本）：

SELECT s.id, s.classDate, s.instructor, tmpE.c AS enrolled, tmpW.c AS waiting
FROM
  schedule AS s,
  ( SELECT scheduleID, COUNT(*) AS c FROM enrolled GROUP BY scheduleID ) AS tmpE,
  ( SELECT scheduleID, COUNT(*) AS c FROM waiting GROUP BY scheduleID ) AS tmpW
WHERE
    s.id = e.scheduleID
    AND s.id = w.scheduleID
GROUP BY s.id

不过，我可能错过了其中的左连接。