SQL组函数的无效使用

Question

SELECT TIME(ADDTIME(departure, SEC_TO_TIME(SUM(jptl2.run_time)))) AS departure,
       service.line_name AS line,
       bus.registration
FROM vehicle_journey
LEFT JOIN bus ON bus.journey = vehicle_journey.code AND
          ADDTIME(TIME(NOW()), SEC_TO_TIME(3600)) > vehicle_journey.departure AND
          ADDTIME(TIME(NOW()), SEC_TO_TIME(3600)) < ADDTIME(vehicle_journey.departure, SEC_TO_TIME(SUM(jptl.run_time)))
INNER JOIN service ON service.reference = vehicle_journey.service_reference
INNER JOIN vehicle_journey_days ON vehicle_journey_days.journey_reference = vehicle_journey.reference
INNER JOIN journey_pattern ON journey_pattern.reference = vehicle_journey.journey_pattern_reference
INNER JOIN journey_pattern_timing_link jptl ON jptl.section = journey_pattern.journey_pattern_section
INNER JOIN journey_pattern_timing_link jptl2 ON jptl2.section = journey_pattern.journey_pattern_section AND jptl2.ID < jptl.id
WHERE jptl.stop = '1900HA030334' AND vehicle_journey_days.day = 'MondayToFriday'
GROUP BY vehicle_journey.id

上面的SQL查询返回：#1111 - Invalid use of group function

我如何重写查询，使其基于相同的需求保存相同的目的和连接，但不会出现组函数错误？

例如：(SELECT SEC_TO_TIME(SUM(jptl.run_time)))

Answer 1

不能在[LEFT] JOIN条件中使用group函数，因为在此阶段，组仍在构建过程中(这些组的内容取决于连接条件，因此这将创建一种循环引用)。

首先构建您的组，然后加入依赖于这些组的表：

SELECT groups.*, bus.registration
FROM (
    SELECT
        TIME(...) AS departure,
        service.line_name AS line,
        ADDTIME(vehicle_journey.departure, SEC_TO_TIME(SUM(jptl.run_time)))
            AS upper_limit,
        vehicle_journey.code
            AS vj_code,
        vehicle_journey.departure
            AS vj_departure
    FROM vehicle_journey
    -- skip "bus" for now
    JOIN service ...
    -- add all remaining JOIN's here
    WHERE ...
    GROUP BY vehicle_journey.id
) AS groups
LEFT JOIN bus
    ON bus.journey = vj_code
    AND ADDTIME(TIME(NOW()), SEC_TO_TIME(3600)) > vj_departure
    AND ADDTIME(TIME(NOW()), SEC_TO_TIME(3600)) < upper_limit;

您还可以简单地将有问题的条件下移到HAVING子句中，但这不会返回bus中没有匹配的记录(有效地将LEFT JOIN转换为INNER JOIN)：

SELECT
    ...
FROM vehicle_journey
LEFT JOIN bus ON bus.journey = vehicle_journey.code AND
      ADDTIME(TIME(NOW()), SEC_TO_TIME(3600)) > vehicle_journey.departure
      -- no reference to "SUM(jptl.run_time)" here
JOIN service ...
WHERE ...
GROUP BY vehicle_journey.id
-- and here is the reference to "SUM(jptl.run_time)"
HAVING ADDTIME(TIME(NOW()), SEC_TO_TIME(3600))
       < ADDTIME(vehicle_journey.departure, SEC_TO_TIME(SUM(jptl.run_time)))