如何合并两个SQL查询结果并按条件过滤出差异数据？

方案1：单SQL直接实现（推荐，无需处理中间结果） #

直接在数据库内完成统计、对比、筛选全流程，执行后直接得到目标结果：

WITH q1_stats AS (
    -- 统计查询1各项目编号出现次数
    SELECT `Project Number`, COUNT(*) AS count1
    FROM (
        -- 直接粘贴你的查询1完整语句
        select `Project Number` from vw_onco_pharma onco_pharma 
        union select `Project Number` from vw_onco_cell_gene cell_gene 
        union select `Project Number` from vw_non_onco_cell_gene onco_cell_gene 
        union select `Project Number` from vw_non_onco_pharma non_onco_pharma 
        union select `Project Number` from vw_plasma_protein plasma_protein
    ) q1_raw
    GROUP BY `Project Number`
),
q2_stats AS (
    -- 统计查询2各项目编号出现次数
    SELECT `Project Number`, COUNT(*) AS count2
    FROM FCT_HTA_ONC_NONONC_PGMS
    GROUP BY `Project Number`
)
-- 筛选存在差异的项目编号
SELECT COALESCE(q1.`Project Number`, q2.`Project Number`) AS `Project Number`
FROM q1_stats q1
FULL OUTER JOIN q2_stats q2 
    ON q1.`Project Number` = q2.`Project Number`
WHERE 
    q1.`Project Number` IS NULL -- 仅在查询2中存在的项目
    OR q2.`Project Number` IS NULL -- 仅在查询1中存在的项目
    OR q1.count1 <> q2.count2; -- 两边都存在但出现次数不一致的项目

如果使用的数据库不支持FULL OUTER JOIN（比如低版本MySQL），可以用以下替代写法：

WITH q1_stats AS (
    SELECT `Project Number`, COUNT(*) AS count1
    FROM (
        select `Project Number` from vw_onco_pharma onco_pharma 
        union select `Project Number` from vw_onco_cell_gene cell_gene 
        union select `Project Number` from vw_non_onco_cell_gene onco_cell_gene 
        union select `Project Number` from vw_non_onco_pharma non_onco_pharma 
        union select `Project Number` from vw_plasma_protein plasma_protein
    ) q1_raw
    GROUP BY `Project Number`
),
q2_stats AS (
    SELECT `Project Number`, COUNT(*) AS count2
    FROM FCT_HTA_ONC_NONONC_PGMS
    GROUP BY `Project Number`
)
SELECT `Project Number` FROM q1_stats
WHERE NOT EXISTS (SELECT 1 FROM q2_stats WHERE q2_stats.`Project Number` = q1_stats.`Project Number` AND q2_stats.count2 = q1_stats.count1)
UNION
SELECT `Project Number` FROM q2_stats
WHERE NOT EXISTS (SELECT 1 FROM q1_stats WHERE q1_stats.`Project Number` = q2_stats.`Project Number` AND q1_stats.count1 = q2_stats.count2);

方案2：已导出数据本地处理 #

如果你已经把两个查询的结果导出到了Excel/表格工具，可以按以下步骤操作：

• 分别对两个导出结果的Project Number列用COUNTIF函数统计每个编号的出现次数
• 用VLOOKUP/XLOOKUP匹配两个统计结果，筛选匹配失败、或者次数数值不一致的行即可得到目标结果

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