Mysql[5.7]嵌套选择查询无法识别父表

Question

Mysql 5.7 嵌套选择查询抛出错误

Unknown column 'CRoute.ID' in 'where clause'

这是我的查询

SELECT CTrip.ID, CRoute.Name, 
(SELECT COUNT(StudentID) FROM CRouteStudent WHERE CRoute.ID = CRouteStudent.RouteID) AS ExpectedStudents,
(SELECT COUNT(*) FROM (SELECT COUNT(*)  FROM CRouteStudent WHERE CRoute.ID = CRouteStudent.RouteID GROUP BY Stop)  as a) AS ExpectedStops
FROM `CTrip`
JOIN `CRoute` ON `CRoute`.`ID` = `CTrip`.`RouteID`
JOIN `CRouteSchedule` ON `CRouteSchedule`.`RouteID`=`CTrip`.`RouteID`
WHERE `CTrip`.`Direction` = '1'
LIMIT 30

ExpectedStudents 正在工作，ExpectedStops 抛出错误

注意此查询在 mysql 8.0 中运行

Answer 1

子查询嵌套得很深。

您可以尝试更换

(SELECT COUNT(*) FROM (SELECT COUNT(*)  FROM CRouteStudent WHERE CRoute.ID = CRouteStudent.RouteID GROUP BY Stop)  as a)

与:

(SELECT count(DISTINCT stop)
        FROM croutestudent
        WHERE croute.id = croutestudent.routeid)
+
EXISTS (SELECT *
               FROM croutestudent
               WHERE croute.id = croutestudent.routeid
                     AND stop IS NULL)

如果 stop 不可为空，则可以保留 + EXISTS ... 部分。