合并 UNION ALL 行以删除 NULL 值 [英] Merging UNION ALL rows to remove NULL values

问题描述

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 以便它不会忽略它，也不会将它包含在查询中.

Above is two queries with their results merged by a UNION ALL clause. You will notice that the queries return different columns, one called 'departure' and one called 'arrival'. To get the UNION to work with the different column names I have to give the other column NULL so that it doesn't ignore it and not include it in the query.

我的问题是我的结果如下所示:

My issue is I have results that looks like this:

id |出发|到达
1 asd NULL
2 asd NULL
3 asd NULL
4 asd NULL
5 NULL efg
6 NULL efg
7 NULL efg
8 NULL efg

如何合并行以便 asd 和 efg 根据 ID 匹配?

How can I merge the rows so that asd and efg match up based on the ID?

想要的结果:

id |出发|到达
1 asd efg
2 asd efg
3 asd efg
4 asd efg

推荐答案

只需将聚合函数 (min/max) 应用于您的结果集.由于聚合中不包含空值，因此您只会获得组合结果:

Just apply an aggregate function (min/max) to your result set. As nulls are not included in the aggregate you will just get the combined results:

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

这篇关于合并 UNION ALL 行以删除 NULL 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！