将返回以下SQL脚本；
ER_BAD_FIELD_ERROR:“field list”中的未知列“tams_event_det_f.lastEventDate”。
从初始的select语句中删除LastEventDate可以修复错误，但这也意味着字段不会添加到输出表中。
LastEventDate是在inner join的where子句的内部select查询中声明的，因此它超出了主查询的范围。
如何将LastEventDate带入主查询的范围以便在输出表中显示结果？
SELECT drivers.driver_key,
driver_name,
driver_sitename,
LastEventDate,
SUM(trips.trip_distance)
FROM drivers
INNER JOIN trips ON drivers.driver_key = trips.driver_key
WHERE trips.trip_date > (
SELECT MAX(event_date) AS LastEventDate
FROM events
WHERE events.driver_key = drivers.driver_key
)
GROUP BY DriverKey
ORDER BY DistanceSince DESC;
您可以将子查询转到派生表并将其联接:
SELECT
d.driver_key,
d.driver_name,
d.driver_sitename,
e.last_event_date,
SUM(t.trip_distance) distance_since
FROM drivers d
INNER JOIN trips t ON d.driver_key = t.driver_key
INNER JOIN (
SELECT driver_key, MAX(event_date) AS last_event_date
FROM events
GROUP BY driver_key
) e ON e.driver_key = d.driver_key and t.trip_date > e.last_event_date
GROUP BY d.driver_key, d.driver_name, d.driver_name
ORDER BY distance_since DESC;
旁注:
>
我更改了查询的group by子句，使其包含所有非合并的列（您的原始代码不是有效的标准SQL，尽管MySQL的某些版本默认允许这样的语法）
我添加了表别名，这使得查询更容易读写
内部子查询中的drivers表不能访问外部表引用，因此您应该在子查询中使用正确的内部联接
SELECT
drivers.driver_key,
driver_name,
driver_sitename,
LastEventDate,
SUM(trips.trip_distance)
FROM drivers
INNER JOIN trips
ON drivers.driver_key = trips.driver_key
WHERE trips.trip_date > (
SELECT
MAX(event_date) AS LastEventDate
FROM events
INNER JOIN drivers on events.driver_key = drivers.driver_key
)
GROUP BY DriverKey
ORDER BY DistanceSince DESC;
但也可以对max值使用适当的内部联接
SELECT
drivers.driver_key,
driver_name,
driver_sitename,
LastEventDate,
SUM(trips.trip_distance)
FROM drivers
INNER JOIN trips ON drivers.driver_key = trips.driver_key
INNER JOIN (
SELECT
driver_key, MAX(event_date) AS LastEventDate
FROM events
INNER JOIN drivers ON events.driver_key = drivers.driver_key
group by driver_key
) t t.driver_key = drivers.driver_key
AND trips.trip_date > t.LastEventDate
GROUP BY DriverKey
ORDER BY DistanceSince DESC;
