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

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中有效

最佳答案

子查询嵌套得很深。

您可以尝试更换

(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)

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