如何不使用MAX函数重写指定SQL语句并实现预期结果？

原SQL语句：

select QID, max(DT), max(city)
from (
  select '1' as QID,  '' as DT, 'NYC' as city
  union
  select '1' as QID, '2023-04-18' as DT, '' as city )
group by QID

预期输出：

方案1：自连接合并非空值 #

由于每个QID下正好一行是DT非空、一行是city非空，直接通过自连接把两行的有效字段拼起来就行：

SELECT t1.QID, t2.DT, t1.city
FROM (
  SELECT '1' AS QID, 'NYC' AS city
) t1
JOIN (
  SELECT '1' AS QID, '2023-04-18' AS DT
) t2 ON t1.QID = t2.QID

这种写法逻辑直白，完全贴合当前的数据结构，性能也最优。

方案2：窗口函数筛选非空行 #

如果需要兼容更通用的场景（比如QID下可能有多行数据，但只需要取每个字段的第一个非空值），可以用窗口函数标记优先级：

WITH cte AS (
  SELECT 
    QID, DT, city,
    -- 给DT非空的行排优先级1，空的排2
    ROW_NUMBER() OVER(PARTITION BY QID ORDER BY CASE WHEN DT <> '' THEN 1 ELSE 2 END) AS rn_dt,
    -- 给city非空的行排优先级1，空的排2
    ROW_NUMBER() OVER(PARTITION BY QID ORDER BY CASE WHEN city <> '' THEN 1 ELSE 2 END) AS rn_city
  FROM (
    SELECT '1' as QID,  '' as DT, 'NYC' as city
    UNION
    SELECT '1' as QID, '2023-04-18' as DT, '' as city 
  ) t
)
SELECT 
  c1.QID,
  c2.DT,
  c1.city
FROM cte c1
JOIN cte c2 ON c1.QID = c2.QID
WHERE c1.rn_city = 1 AND c2.rn_dt = 1

这个写法不需要MAX函数，通过窗口函数优先选中非空行，再关联得到目标值，扩展性更好。

方案3：直接构造结果（仅适用于固定数据） #

如果你的数据是硬编码固定不变的，那直接写目标结果最省事：

SELECT '1' AS QID, '2023-04-18' AS DT, 'NYC' AS city

当然这种写法没有通用性，只适合示例这种固定数据的场景。

内容的提问来源于stack exchange，提问作者user3700039