PostgreSQL中UNION查询返回重复ID问题及解决需求

执行以下SQL查询时，结果出现重复ID（例如ID=2同时对应data2和null）：

select a.id , b.value  
from tableB b
JOIN  tableA a 
ON b.id_data = a.id_data 
JOIN tableC c 
ON b.id_data = c.id_data
WHERE b.place ='city' AND c.roll  = '20' 
UNION 
select c.id,  COALESCE(c.value, cast(c.number as text),cast(c.date as text)) 
FROM tableC c
where c.roll  = '15'

预期结果 #

希望得到ID唯一的两列数据：

实际结果 #

实际返回结果存在重复ID：

尝试使用DISTINCT仅能得到单列唯一ID，无法保留对应的value列。以下是几种可行的解决方法：

方法1：使用窗口函数筛选优先记录 #

通过ROW_NUMBER()给每个ID分组，优先保留非null的value记录：

WITH combined_data AS (
    SELECT a.id, b.value
    FROM tableB b
    JOIN tableA a ON b.id_data = a.id_data
    JOIN tableC c ON b.id_data = c.id_data
    WHERE b.place = 'city' AND c.roll = '20'
    UNION
    SELECT c.id, COALESCE(c.value, cast(c.number as text), cast(c.date as text)) AS value
    FROM tableC c
    WHERE c.roll = '15'
)
SELECT id, value
FROM (
    SELECT 
        id, 
        value,
        -- 非null值排前面，每组取第一条
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY CASE WHEN value IS NOT NULL THEN 0 ELSE 1 END) AS rn
    FROM combined_data
) t
WHERE rn = 1
ORDER BY id;

方法2：使用聚合函数自动忽略null #

利用MAX()聚合函数忽略null的特性，直接对每个ID取非null的value：

SELECT 
    id,
    MAX(value) AS value
FROM (
    SELECT a.id, b.value
    FROM tableB b
    JOIN tableA a ON b.id_data = a.id_data
    JOIN tableC c ON b.id_data = c.id_data
    WHERE b.place = 'city' AND c.roll = '20'
    UNION
    SELECT c.id, COALESCE(c.value, cast(c.number as text), cast(c.date as text)) AS value
    FROM tableC c
    WHERE c.roll = '15'
) combined_data
GROUP BY id
ORDER BY id;

方法3：通过JOIN优先保留第一部分数据 #

将两部分查询拆分为CTE，用FULL OUTER JOIN合并后，优先取第一部分（非null）的value：

WITH part1 AS (
    SELECT a.id, b.value
    FROM tableB b
    JOIN tableA a ON b.id_data = a.id_data
    JOIN tableC c ON b.id_data = c.id_data
    WHERE b.place = 'city' AND c.roll = '20'
),
part2 AS (
    SELECT c.id, COALESCE(c.value, cast(c.number as text), cast(c.date as text)) AS value
    FROM tableC c
    WHERE c.roll = '15'
)
SELECT 
    COALESCE(p1.id, p2.id) AS id,
    COALESCE(p1.value, p2.value) AS value
FROM part1 p1
FULL OUTER JOIN part2 p2 ON p1.id = p2.id
ORDER BY id;

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