mysql - 从 MySQL 中的 2 个表中执行 SELECT COUNT

Question

我有 2 个表 STUDENT 和 COURSE。

STUDENT 有以下列:

SID INTERGER
NAME VARCHAR
DEPARTMENT INTEGER
REGISTRATIONDATE DATE

COURSE 有以下列:

CID INTERGER
SID INTERGER
ENROLLEDATE 

我想按部门获得每个注册日期的学生总数，以及该注册日期每门类(class)注册的学生人数，这可以通过注册日期和注册日期 + 28 之间的注册来确定

如果我的输入数据是:

STUDENT:
1,John,CS,11/01/2014
2,Jim,CS,11/01/2014
3,Jane,LAW,10/01/2014
4,Rose,Engineering,11/01/2014

COURSE:
1,1,11/10/2014
1,2,11/11/2014
2,3,11/11/2014

OUTPUT:
StudentCount,Department,RegistrationDate,StudentInCourseCount
2,CS,11/01/2014,2
1,LAW,10/01/2014,1
1,Engineering,11/01/2014,

StudentCount = 每个注册日期在一个系注册的学生人数

部门 = 该计数的部门名称

RegistrationDate = 每个唯一的注册期

StudentInCourseCount = 在 REGISTRATIONDATE 和 REGISTRATIONDATE 之间注册的每门类(class)的学生人数 + 28

注意:OUTPUT 中最后一行的 StudentInCourseCount 为空，因为 Rose 没有注册任何类(class)。

已尽力解释清楚。

这是我的尝试，但 StudentInCourseCount 不正确，

SELECT COUNT(sd.SID) As STUDENTCOUNT,sd.DEPARTMENT,sd.REGISTRATIONDATE,
(
    SELECT COUNT(p.SID) 
    FROM COURSE p
    WHERE sd.SID=p.SID AND p.ENROLLDATE BETWEEN sd.REGISTRATIONDATE AND sd.REGISTRATIONDATE+28 
 )  AS StudentInCourseCount
FROM STUDENT sd
GROUP BY DEPARTMENT,REGISTRATIONDATE
ORDER BY REGISTRATIONDATE DESC;

谢谢。

Answer 1

试试这个。使用 Conditional Aggregate 逻辑查找 StudentInCourseCount 。更新:Osa E 是对的，它应该是 Distinct Count。

SELECT Count(DISTINCT s.SID) STUDENTCOUNT,
       sd.DEPARTMENT,
       sd.REGISTRATIONDATE,
       Count(CASE
               WHEN ENROLLDATE BETWEEN REGISTRATIONDATE AND REGISTRATIONDATE + 28 THEN 1
             END) StudentInCourseCount
FROM   STUDENT S
       LEFT JOIN COURSE C
         ON S.SID = C.SID
GROUP  BY DEPARTMENT,
          REGISTRATIONDATE