如何用单条SQL查询获取表中所有字段的唯一值？

可以实现你要的效果，核心思路是把每个字段的唯一值单独提取并生成行号，再通过行号将三个集合对齐拼接，没有对应值的位置用空填充。

原问题中的数据 #

Person表数据：

具体SQL实现 #

以支持窗口函数和FULL OUTER JOIN的数据库（比如PostgreSQL、SQL Server）为例：

SELECT
    COALESCE(un.name, '') AS name,
    COALESCE(ua.age::TEXT, '') AS age, -- 转为文本避免类型不一致
    COALESCE(uc.city, '') AS city
FROM
    -- 提取唯一姓名并生成行号
    (SELECT name, ROW_NUMBER() OVER () AS rn FROM (SELECT DISTINCT name FROM Person) AS t) un
FULL OUTER JOIN
    -- 提取唯一年龄并生成行号
    (SELECT age, ROW_NUMBER() OVER () AS rn FROM (SELECT DISTINCT age FROM Person) AS t) ua 
    ON un.rn = ua.rn
FULL OUTER JOIN
    -- 提取唯一城市并生成行号
    (SELECT city, ROW_NUMBER() OVER () AS rn FROM (SELECT DISTINCT city FROM Person) AS t) uc 
    ON COALESCE(un.rn, ua.rn) = uc.rn
ORDER BY COALESCE(un.rn, ua.rn, uc.rn);

如果是MySQL（不支持FULL OUTER JOIN），可以用LEFT JOIN结合UNION ALL补全多余的行：

-- 先取姓名、年龄、城市按行号对齐的部分
SELECT
    IFNULL(un.name, '') AS name,
    IFNULL(ua.age, '') AS age,
    IFNULL(uc.city, '') AS city
FROM
    (SELECT name, ROW_NUMBER() OVER () AS rn FROM (SELECT DISTINCT name FROM Person) AS t) un
LEFT JOIN
    (SELECT age, ROW_NUMBER() OVER () AS rn FROM (SELECT DISTINCT age FROM Person) AS t) ua ON un.rn = ua.rn
LEFT JOIN
    (SELECT city, ROW_NUMBER() OVER () AS rn FROM (SELECT DISTINCT city FROM Person) AS t) uc ON un.rn = uc.rn
-- 补全年龄中超出姓名数量的行
UNION ALL
SELECT
    '',
    ua.age,
    ''
FROM
    (SELECT age, ROW_NUMBER() OVER () AS rn FROM (SELECT DISTINCT age FROM Person) AS t) ua
WHERE ua.rn > (SELECT COUNT(DISTINCT name) FROM Person)
-- 补全城市中超出姓名和年龄最大行号的行
UNION ALL
SELECT
    '',
    '',
    uc.city
FROM
    (SELECT city, ROW_NUMBER() OVER () AS rn FROM (SELECT DISTINCT city FROM Person) AS t) uc
WHERE uc.rn > GREATEST((SELECT COUNT(DISTINCT name) FROM Person), (SELECT COUNT(DISTINCT age) FROM Person))
ORDER BY rn;

为什么DISTINCT和UNION无法满足需求 #

• DISTINCT是对整行去重，只会保留所有字段组合唯一的行，无法实现每个字段单独取唯一值的效果。
• UNION是合并多个结果集，要求每个结果集的列数和类型一致，本质还是按行合并，没法将三个独立的唯一值集合按列对齐。

内容的提问来源于stack exchange，提问作者Александр Коромыслов