时间戳

首页 > 解决方案 > Mysql[5.7] 嵌套选择查询无法识别父表

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 中工作

标签： mysqlmysql-5.7

解决方案

子查询嵌套得很深。

你可以尝试更换

(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不可为空，则可以将其保留。

推荐阅读