合并UNION ALL行以删除空值

Question

SELECT journey.id, TIME_FORMAT(ADDTIME(journey.departure, SEC_TO_TIME(SUM(l1.elapsed))), '%H:%i') AS departure, null AS arrival
FROM journey
INNER JOIN journey_day ON journey_day.journey = journey.code
INNER JOIN pattern ON pattern.code = journey.pattern
INNER JOIN link l1 ON l1.section = pattern.section AND l1.stop = "370023139"
INNER JOIN link l2 ON l2.section = pattern.section AND l2.id <= l1.id
WHERE journey.service = "11-252-_-y08-1" AND journey_day.day = 1 AND journey.code NOT IN (SELECT journey
                                                                                          FROM journey_non_operation
                                                                                          WHERE "2015-03-01" BETWEEN date_start AND date_end) AND pattern.direction = "outbound"
GROUP BY journey.id

UNION ALL

SELECT journey.id, null AS departure, TIME_FORMAT(ADDTIME(journey.departure, SEC_TO_TIME(SUM(l1.elapsed))), '%H:%i') AS arrival
FROM journey
INNER JOIN journey_day ON journey_day.journey = journey.code
INNER JOIN pattern ON pattern.code = journey.pattern
INNER JOIN link l1 ON l1.section = pattern.section AND l1.stop = "1000DEHS7812"
INNER JOIN link l2 ON l2.section = pattern.section AND l2.id <= l1.id
WHERE journey.service = "11-252-_-y08-1" AND journey_day.day = 1 AND journey.code NOT IN (SELECT journey
                                                                                          FROM journey_non_operation
                                                                                          WHERE "2015-03-01" BETWEEN date_start AND date_end) AND pattern.direction = "outbound"
GROUP BY journey.id

上面是两个查询，它们的结果由UNION ALL子句合并。您将注意到，查询返回不同的列，一个名为“离开”，另一个名为“到达”。为了让UNION使用不同的列名，我必须为另一个列指定NULL，这样它就不会忽略它，也不会在查询中包含它。

我的问题是我的结果看起来像这样：

id | departure | arrival

1 asd NULL

2 asd NULL

3 asd NULL

4 asd NULL

5 NULL efg

6 NULL efg

7 NULL efg

8 NULL efg

如何合并这些行，以便根据ID匹配asd和efg？

期望的结果：

id | departure | arrival

1 asd efg

2 asd efg

3 asd efg

4 asd efg

Answer 1

只需对结果集应用聚合函数(min/max)即可。由于nulls不包括在聚合中，因此您将仅获得组合结果：

select id, min(departure), min(arrival)
from (your query) as q
group by id

Answer 2

我不知道你为什么要尝试建立联盟。

尝试这样做：

SELECT journey.id, 
    TIME_FORMAT(ADDTIME(journey.departure, SEC_TO_TIME(SUM(l1.elapsed))), '%H:%i') AS departure, 
    TIME_FORMAT(ADDTIME(journey.departure, SEC_TO_TIME(SUM(l2.elapsed))), '%H:%i') AS arrival
FROM journey
INNER JOIN pattern 
ON pattern.code = journey.pattern
INNER JOIN link l1 
ON l1.section = pattern.section 
    AND l1.stop = "370023139"
INNER JOIN link l2 
ON l2.section = pattern.section 
    AND l2.stop = "1000DEHS7812"
    AND l2.id <= l1.id
WHERE journey.service = "11-252-_-y08-1" 
    AND journey_day.day = 1 
    AND journey.code NOT IN (
        SELECT journey
        FROM journey_non_operation
        WHERE "2015-03-01" BETWEEN date_start AND date_end) 
    AND pattern.direction = "outbound"
GROUP BY journey.id