Question

提取 MySQL SELECT 语句中 WHERE 子句的一部分的结果

2020-06-14

150

mysql sql join select subquery

以下 SQL 脚本返回；

ER_BAD_FIELD_ERROR: Unknown column 'tams_event_det_f.LastEventDate' in 'field list'.

从初始 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;

Answer 1

您可以将子查询转换为派生表并将其连接起来：

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 默认允许这种语法）
我添加了表别名，这使查询更易于读写

Answer 2

内部子查询中的驱动程序表无法访问外部表引用，因此您应该在子查询中使用适当的内部连接

 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;

但也可以使用适当的内部连接来获取最大值

 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;