我正在尝试编写一个SQL,它将允许我返回开始时间的最小值和结束时间的最大值。我的问题是,在表中,我可以有空行,当我对空字段执行MIN时,它会返回一个空值。我不能执行begin_service! = ''
,因为我可能没有值,在这种情况下,我必须有一个空结果。这是我的桌子:
app_id | function_id | begin_service | end_service|
---|---|---|---|
B125 | 12 | ||
B125 | 13 | ||
B125 | 54 | ||
C789 | 98 | ||
C789 | 12 | 06:00 | 18:00|
C789 | 15 | 08:00 | 20:00 |
C789 | 78 |
使用两个子查询来获取每个app_id
的空和非空最小值。将它们与UNION
组合,然后取其中的最大值以选择非空值。
SELECT app_id, MAX(begin_service) AS begin_service, MAX(end_service) AS end_service
FROM (
SELECT app_id, MIN(begin_service) AS begin_service, MAX(end_service) AS end_service
FROM applications
WHERE begin_service != ''
GROUP BY app_id
UNION ALL
SELECT app_id, '', MAX(end_service)
FROM applications
WHERE begin_service = ''
GROUP BY app_id
) AS x
GROUP BY app_id