代码之家 › 专栏 › 技术社区 › Adam Lassek

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

rowcount mysql sql

1

Adam Lassek · 技术社区 · 17 年前

计划包含计划的类。当用户注册某个类时,其用户帐户id和计划类的id存储在“已注册”中。如果一个类已满,它们将存储在等待列表中。所有三个表共享一个scheduleId列,该列标识每个类。

当我查询schedule表时,我还需要返回Registered和waitlist列,这些列表示注册并等待特定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

这对一个或另一个都可以,但显然我无法通过这种方式获得已注册表和等待列表表的值。

3 回复 | 直到 17 年前

1

4

lc. 17 年前

使用嵌套的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

2

3

cmsjr 17 年前

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

3

0

kyle 17 年前

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