sql—提取作为where子句一部分的mysql select语句的结果

sycxhyv7  于 2021-08-09  发布在  Java
关注(0)|答案(2)|浏览(400)

下面的sql脚本返回;
er\ U bad\ field\错误:“字段列表”中的未知列“tams\ u event\ u det\ f.lasteventdate”。
正在删除 LastEventDate 从最初 SELECT 语句修复了错误,但也意味着该字段将不会添加到输出表中。 LastEventDate 在内部 SELECT 查询 WHERE 合同条款 INNER JOIN ,从而使其超出主查询的范围。
我怎么能带你来 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;
ss2ws0br

ss2ws0br1#

可以将子查询转换为派生表并将其联接:

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的某些版本默认允许这种语法)
我添加了表别名,这使查询更易于读写

368yc8dk

368yc8dk2#

内部子查询中的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;

但也可以对最大值使用适当的内部联接

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;

相关问题