MySQL 5.7嵌套查询报错：where子句未知列‘CRoute.ID’

我来帮你搞定这个问题！你遇到的错误是因为旧版本MySQL（低于8.0）不支持多层嵌套子查询跨层引用外部表的列——外层的CRoute.ID无法被最内层的子查询识别，而MySQL 8.0优化了关联子查询的作用域解析，所以能正常执行。

两种可行的解决方案： #

方案1：简化子查询，用COUNT(DISTINCT)替代多层嵌套

你的ExpectedStops本质是统计对应线路下有多少个不同的站点（Stop），完全可以用COUNT(DISTINCT Stop)直接计算，避免多层嵌套的作用域问题：

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

方案2：预计算站点数，通过JOIN引入主查询

如果需要保留原有的分组逻辑（比如后续要扩展统计逻辑），可以先提前计算每个线路的站点数，再和主查询关联：

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

如果你的MySQL版本不支持CTE（WITH子句），可以把CTE换成派生表：

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

为什么原来的写法在MySQL 8.0能跑？ #

MySQL 8.0引入了对关联子查询多层作用域解析的支持，允许内层子查询引用外层查询中更深层次的表列，而旧版本的MySQL会把最内层的子查询当成独立的查询，无法识别外层的CRoute.ID。

内容的提问来源于stack exchange，提问作者Fazlan Ahamed