MySQL集合运算结果合并查询：如何同时返回差集与交集数据

适用于MySQL 8.0及以上版本的解法 #

WITH all_rows AS (
    -- 生成覆盖三个结果集最大行数的行号序列
    SELECT 1 AS rn
    UNION ALL
    SELECT rn + 1 FROM all_rows WHERE rn < (
        SELECT GREATEST(
            (SELECT COUNT(*) FROM (
                SELECT CONCAT(RESOURCE, " AMB:", AMB) FROM db.CRE
                WHERE CONTENT = '183' AND LEVEL = '99'
                EXCEPT
                SELECT CONCAT(RESOURCE, " AMB:", AMB) FROM db.CRE
                WHERE CONTENT = '182' AND LEVEL = '99'
            ) t1),
            (SELECT COUNT(*) FROM (
                SELECT CONCAT(RESOURCE, " AMB:", AMB) FROM db.CRE
                WHERE CONTENT = '183' AND LEVEL = '99'
                INTERSECT
                SELECT CONCAT(RESOURCE, " AMB:", AMB) FROM db.CRE
                WHERE CONTENT = '182' AND LEVEL = '99'
            ) t2),
            (SELECT COUNT(*) FROM (
                SELECT CONCAT(RESOURCE, " AMB:", AMB) FROM db.CRE
                WHERE CONTENT = '182' AND LEVEL = '99'
                EXCEPT
                SELECT CONCAT(RESOURCE, " AMB:", AMB) FROM db.CRE
                WHERE CONTENT = '183' AND LEVEL = '99'
            ) t3)
        )
    )
),
rec1_data AS (
    -- 仅存在于CONTENT='183'的结果，添加行号
    SELECT 
        CONCAT(RESOURCE, " AMB:", AMB) AS rec1,
        ROW_NUMBER() OVER () AS rn
    FROM (
        SELECT CONCAT(RESOURCE, " AMB:", AMB) FROM db.CRE
        WHERE CONTENT = '183' AND LEVEL = '99'
        EXCEPT
        SELECT CONCAT(RESOURCE, " AMB:", AMB) FROM db.CRE
        WHERE CONTENT = '182' AND LEVEL = '99'
    ) t
),
rec_same_data AS (
    -- 两组共有的交集结果，添加行号
    SELECT 
        CONCAT(RESOURCE, " AMB:", AMB) AS rec_same,
        ROW_NUMBER() OVER () AS rn
    FROM (
        SELECT CONCAT(RESOURCE, " AMB:", AMB) FROM db.CRE
        WHERE CONTENT = '183' AND LEVEL = '99'
        INTERSECT
        SELECT CONCAT(RESOURCE, " AMB:", AMB) FROM db.CRE
        WHERE CONTENT = '182' AND LEVEL = '99'
    ) t
),
rec2_data AS (
    -- 仅存在于CONTENT='182'的结果，添加行号
    SELECT 
        CONCAT(RESOURCE, " AMB:", AMB) AS rec2,
        ROW_NUMBER() OVER () AS rn
    FROM (
        SELECT CONCAT(RESOURCE, " AMB:", AMB) FROM db.CRE
        WHERE CONTENT = '182' AND LEVEL = '99'
        EXCEPT
        SELECT CONCAT(RESOURCE, " AMB:", AMB) FROM db.CRE
        WHERE CONTENT = '183' AND LEVEL = '99'
    ) t
)
-- 按行号关联三个结果集，空结果对应列显示NULL
SELECT 
    r1.rec1,
    rs.rec_same,
    r2.rec2
FROM all_rows ar
LEFT JOIN rec1_data r1 ON ar.rn = r1.rn
LEFT JOIN rec_same_data rs ON ar.rn = rs.rn
LEFT JOIN rec2_data r2 ON ar.rn = r2.rn;

说明

• 通过CTE生成连续行号序列，确保覆盖三个结果集中的最大行数
• 给每个独立查询的结果添加行号，再通过LEFT JOIN按行号关联，实现三列并行展示
• 若某一结果集为空，对应列会显示NULL，不会导致查询失败

适用于MySQL 5.7及以下版本的解法 #

-- 先定义变量用于生成行号
SET @rn1 := 0;
SET @rn2 := 0;
SET @rn3 := 0;

-- 生成行号序列（这里假设最大行数不超过100，可按需扩展）
SELECT 
    r1.rec1,
    rs.rec_same,
    r2.rec2
FROM (
    SELECT 1 AS rn UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL 
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL 
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
) ar
LEFT JOIN (
    -- 仅存在于CONTENT='183'的结果
    SELECT 
        CONCAT(RESOURCE, " AMB:", AMB) AS rec1,
        @rn1 := @rn1 + 1 AS rn
    FROM db.CRE c1
    WHERE c1.CONTENT = '183' AND c1.LEVEL = '99'
    AND NOT EXISTS (
        SELECT 1 FROM db.CRE c2
        WHERE c2.CONTENT = '182' AND c2.LEVEL = '99'
        AND c2.RESOURCE = c1.RESOURCE AND c2.AMB = c1.AMB
    )
) r1 ON ar.rn = r1.rn
LEFT JOIN (
    -- 两组共有的交集结果
    SELECT 
        CONCAT(RESOURCE, " AMB:", AMB) AS rec_same,
        @rn2 := @rn2 + 1 AS rn
    FROM db.CRE c1
    WHERE c1.CONTENT = '183' AND c1.LEVEL = '99'
    AND EXISTS (
        SELECT 1 FROM db.CRE c2
        WHERE c2.CONTENT = '182' AND c2.LEVEL = '99'
        AND c2.RESOURCE = c1.RESOURCE AND c2.AMB = c1.AMB
    )
) rs ON ar.rn = rs.rn
LEFT JOIN (
    -- 仅存在于CONTENT='182'的结果
    SELECT 
        CONCAT(RESOURCE, " AMB:", AMB) AS rec2,
        @rn3 := @rn3 + 1 AS rn
    FROM db.CRE c1
    WHERE c1.CONTENT = '182' AND c1.LEVEL = '99'
    AND NOT EXISTS (
        SELECT 1 FROM db.CRE c2
        WHERE c2.CONTENT = '183' AND c2.LEVEL = '99'
        AND c2.RESOURCE = c1.RESOURCE AND c2.AMB = c1.AMB
    )
) r2 ON ar.rn = r2.rn
-- 过滤掉所有列都为NULL的行
WHERE r1.rec1 IS NOT NULL OR rs.rec_same IS NOT NULL OR r2.rec2 IS NOT NULL;

说明

• 用用户变量替代ROW_NUMBER()生成行号
• 用EXISTS/NOT EXISTS替代INTERSECT/EXCEPT
• 手动生成行号序列，若实际结果行数超过定义的数量，需扩展UNION ALL的行数量

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