mysql - SQL子查询返回一列的MIN和另一列的相应值

Question

我正在尝试查询

1. 通过的类(class)数，
2. 最早通过的类(class)
3. 每个当前未被开除的学生通过第一门类(class)所用的时间。

这里棘手的部分是 2)。我通过将类(class)表映射到自身但将匹配限制为仅 datepassed=min(datepassed) 来构建子查询。该查询似乎适用于一个非常样本，但是当我尝试将其应用于我的完整数据集(将返回约 100 万条记录)时，查询执行时间长得不可思议(将其保留 >2 小时但仍然不会完全的)。

有没有更有效的方法来做到这一点？感谢您的帮助!

查询:

SELECT 
  S.id,
  COUNT(C.course) as course_count,
  C2.course as first_course,
  DATEDIFF(MIN(C.datepassed),S.dateenrolled) as days_to_first
FROM student S
LEFT JOIN course C 
  ON C.studentid = S.id
LEFT JOIN (SELECT * FROM course GROUP BY studentid HAVING datepassed IN (MIN(datepassed))) C2
  ON C2.studentid = C.studentid
WHERE YEAR(S.dateenrolled)=2013 
  AND U.id NOT IN (SELECT id FROM expelled)
GROUP BY S.id
ORDER BY S.id

学生表

id  status  dateenrolled
1   graduated   1/1/2013
3   graduated   1/1/2013

驱逐表

id  dateexpelled
2   5/1/2013

类(class)表

studentid   course  datepassed
1   courseA 5/1/2014
1   courseB 1/1/2014
1   courseC 2/1/2014
1   courseD 3/1/2014
3   courseA 1/1/2014
3   couseB  2/1/2014
3   courseC 3/1/2014
3   courseD 4/1/2014
3   courseE 5/1/2014

Answer 1

SELECT id, course_count, days_to_first, C2.course first_course
FROM (
    SELECT S.id, COUNT(C.course) course_count, 
        DATEDIFF(MIN(datepassed),S.dateenrolled) as days_to_first,
        MIN(datepassed) min_datepassed
    FROM student S
        LEFT JOIN course C ON C.studentid = S.id 
    WHERE S.dateenrolled BETWEEN '2013-01-01' AND '2013-12-31'
        AND S.id NOT IN (SELECT id FROM expelled)
    GROUP BY S.id
) t1 LEFT JOIN course C2 
    ON C2.studentid = t1.id
    AND C2.datepassed = t1.min_datepassed
ORDER BY id