mysql - 在嵌套子查询中引用表

如何在WHERE子句的“最外部”表（别名为“ cmp”）中引用ID_comp字段：

SELECT
      (SELECT COUNT(*) cnt
       FROM
           (SELECT trp.trip_no
        FROM Trip trp
        WHERE trp.ID_comp = cmp.ID_comp
        GROUP BY trp.trip_no) foo
      ) flights
FROM Company cmp

上面的代码给了我一个错误："Unknown column 'cmp.ID_comp' in 'where clause'"？

最佳答案

我认为您正在寻找：

SELECT
   C.ID_comp,
   COUNT(T.ID_comp) trip_count
FROM company C
  LEFT JOIN Trip T
    ON C.ID_comp = T.ID_comp
GROUP BY C.ID_comp