如何用SQL实现多列去重统计并生成指定分组结果表？

云上老周

2026-6-21

解决方案

假设你的原始数据表名为airport_pairs，完全可以通过单条SELECT语句完成需求，核心思路是先提取所有出现在两列中的唯一值，再分别判断每个值在对应列中的存在情况。

方法1：子查询+EXISTS判断

SELECT
    aa.airport,
    CASE WHEN EXISTS (SELECT 1 FROM airport_pairs WHERE ColumnA = aa.airport) THEN 1 ELSE 0 END AS 在ColumnA出现,
    CASE WHEN EXISTS (SELECT 1 FROM airport_pairs WHERE ColumnB = aa.airport) THEN 1 ELSE 0 END AS 在ColumnB出现
FROM (
    -- 提取所有出现在ColumnA或ColumnB的唯一机场代码
    SELECT ColumnA AS airport FROM airport_pairs
    UNION
    SELECT ColumnB AS airport FROM airport_pairs
) aa
ORDER BY aa.airport;

方法2：CTE+左连接判断

WITH all_airports AS (
    -- 生成包含所有唯一机场代码的临时结果集
    SELECT ColumnA AS airport FROM airport_pairs
    UNION
    SELECT ColumnB AS airport FROM airport_pairs
)
SELECT
    aa.airport,
    CASE WHEN ap_a.ColumnA IS NOT NULL THEN 1 ELSE 0 END AS 在ColumnA出现,
    CASE WHEN ap_b.ColumnB IS NOT NULL THEN 1 ELSE 0 END AS 在ColumnB出现
FROM all_airports aa
-- 左连接去重后的ColumnA，判断当前机场是否存在于该列
LEFT JOIN (SELECT DISTINCT ColumnA FROM airport_pairs) ap_a 
    ON aa.airport = ap_a.ColumnA
-- 左连接去重后的ColumnB，判断当前机场是否存在于该列
LEFT JOIN (SELECT DISTINCT ColumnB FROM airport_pairs) ap_b 
    ON aa.airport = ap_b.ColumnB
ORDER BY aa.airport;