如何合并输出列不同的两个SQL查询（含Union的Query1与Query2）

云原生阿岚

2026-6-24

合并列不匹配的两个SQL查询结果

问题概述

现有两个SQL查询，Query1包含Union逻辑，输出字段为total、DOC_TYPE、ACC_NUM、CKAI、COMPOUND、TICKET、P；Query2结构简单，输出字段为DOC_TYPE、TOTAL_NUM_GROUP。尝试用Union合并时因列数/列类型不匹配报错，需求是将Query2的TOTAL_NUM_GROUP字段合并到Query1的结果中，得到包含所有原有字段及新增TOTAL_NUM_GROUP的最终输出。

Query1 代码及输出

代码

SELECT (
        SELECT COUNT(*)
        FROM (
            SELECT DISTINCT el.REFERENCE
            FROM T_LEJ el
            WHERE el.REFERENCE <> 'NUM'
                AND el.REFERENCE <> 'J'
                AND el.REFERENCE <> 'DEBIT'
                AND el.REFERENCE <> 'CREDIT'
                AND el.REFERENCE <> 'COMPOUND'
                AND el.REFERENCE <> 'TICKET'
                AND el.REFERENCE <> 'P'
                AND el.REFERENCE NOT LIKE 'BBK%'
        )
    ) AS total,
    CASE
        WHEN c.DOC_TYPE = '1 RECEIPT' THEN 'RECEIPT'
        ELSE c.DOC_TYPE
    END AS DOC_TYPE,
    c.ACC_NUM,
    NVL(c.CKAI, 0) AS CKAI,
    NVL(c.COMPOUND, 0) AS COMPOUND,
    NVL(c.TICKET, 0) AS TICKET,
    NVL(c.P, 0) AS P
    
FROM (
    SELECT
        *
    FROM (
        SELECT
            '1 RECEIPT' AS DOC_TYPE,
            COUNT(DISTINCT el.ACC_NUM) AS ACC_NUM,
            SUM(CASE WHEN el.REF_CODE = '61101' THEN NVL(SUM(el.CREDIT - el.DEBIT), 0) END) AS CKAI,
            SUM(CASE WHEN el.REF_CODE = '76101' THEN NVL(SUM(el.CREDIT - el.DEBIT), 0) END) AS COMPOUND,
            SUM(CASE WHEN el.REF_CODE = '76102' THEN NVL(SUM(CREDIT - DEBIT), 0) END) AS TICKET,
            SUM(CASE WHEN el.REF_CODE = '76103' THEN NVL(SUM(CREDIT - DEBIT), 0) END) AS P
        FROM T_LEJ el
        WHERE el.REFERENCE = 'RECEIPT'
        AND el.TRK_TRANS BETWEEN TO_DATE('01/01/2024', 'DD/MM/YYYY') AND TO_DATE('31/01/2024', 'DD/MM/YYYY')
        GROUP BY el.REF_CODE, el.CREDIT, el.DEBIT, el.ACC_NUM
    ) A
    UNION
    SELECT
        *
    FROM (
        SELECT
            SUBSTR(CONCAT(ra.SF, CONCAT(' | ', ra.DETAIL)), 0, 35) AS DOC_TYPE,
            COUNT(DISTINCT lej.ACC_NUM) AS ACC_NUM,
            NVL(SUM(lej.CKAI), 0) AS CKAI,
            NVL(SUM(lej.COMPOUND), 0) AS COMPOUND,
            NVL(SUM(lej.TICKET), 0) AS TICKET,
            NVL(SUM(lej.P), 0) AS P
        FROM (
            SELECT DISTINCT el.REFERENCE
            FROM T_LEJ el
            WHERE el.REFERENCE <> 'NUM'
                AND el.REFERENCE <> 'J'
                AND el.REFERENCE <> 'DEBIT'
                AND el.REFERENCE <> 'CREDIT'
                AND el.REFERENCE <> 'COMPOUND'
                AND el.REFERENCE <> 'TICKET'
                AND el.REFERENCE <> 'P'
                AND el.REFERENCE <> 'RECEIPT'
                AND el.REFERENCE NOT LIKE 'BBK%'
        ) ruj
        LEFT JOIN REF_AGENCY ra ON ruj.REFERENCE = ra.SF
        LEFT JOIN (
            SELECT
                el.REFERENCE,
                el.ACC_NUM,
                CASE
                    WHEN el.REF_CODE = '61101' THEN NVL(SUM(CREDIT - DEBIT), 0)
                END AS CKAI,
                CASE
                    WHEN el.REF_CODE = '76101' THEN NVL(SUM(CREDIT - DEBIT), 0)
                END AS COMPOUND,
                CASE
                    WHEN el.REF_CODE = '76102' THEN NVL(SUM(CREDIT - DEBIT), 0)
                END AS TICKET,
                CASE
                    WHEN el.REF_CODE = '76103' THEN NVL(SUM(CREDIT - DEBIT), 0)
                END AS P
            FROM REF_AGENCY ra
            LEFT JOIN T_LEJ el ON ra.SF = el.REFERENCE
            WHERE el.TRK_TRANS BETWEEN TO_DATE('01/01/2024', 'DD/MM/YYYY') AND TO_DATE('31/01/2024', 'DD/MM/YYYY')
            GROUP BY el.REFERENCE, el.CREDIT, el.DEBIT, el.REF_CODE, el.ACC_NUM
        ) lej ON ra.SF = lej.REFERENCE
        GROUP BY ra.SF, ra.DETAIL
    ) B
) C
ORDER BY c.DOC_TYPE;

输出说明

Query1输出包含以下字段：

total：统计符合条件的REFERENCE数量
DOC_TYPE：单据类型
ACC_NUM：账户数量
CKAI、COMPOUND、TICKET、P：各类交易金额统计

Query2 代码及输出

代码

SELECT
    DOC_TYPE,
    SUM(e.NUM_GROUP) AS TOTAL_NUM_GROUP
FROM (
    SELECT
        DISTINCT e.GROUP_ID,
        SUBSTR(CONCAT(ra.SF, CONCAT(' | ', ra.REFERENCE)), 0, 35) AS DOC_TYPE,
        e.NUM_GROUP
    FROM
        T_TRANSACTION e
    LEFT JOIN
        T_TRANSACTION_DET ed ON e.GROUP_ID = ed.GROUP_ID
    LEFT JOIN
        T_LEJ el ON ed.ACC_NUM = el.ACC_NUM
    LEFT JOIN
        REF_AGENCY ra ON el.REFERENCE = ra.SF
    WHERE
        e.COLL_CENTER = ra.SF
        AND 
        e.TRK_KLMPK >= TO_DATE('2024-01-01', 'YYYY-MM-DD')
        AND e.TRK_KLMPK <= TO_DATE('2024-01-31', 'YYYY-MM-DD')
) e
GROUP BY
    DOC_TYPE
ORDER BY 
    DOC_TYPE;

输出说明

Query2按DOC_TYPE分组，输出每个单据类型对应的TOTAL_NUM_GROUP（分组数量统计）。

预期输出

在Query1的所有字段基础上，新增TOTAL_NUM_GROUP字段，该字段的值为Query2中对应DOC_TYPE的统计结果；若Query1的某个DOC_TYPE在Query2中无匹配数据，该字段显示0或NULL（可按需处理）。

解决方案：通过左连接合并结果

将Query1和Query2分别作为子查询，通过DOC_TYPE字段进行左连接，确保Query1的所有行都被保留，同时关联Query2的统计数据。以下是合并后的完整SQL：

-- 主查询：将Query1与Query2的结果通过DOC_TYPE左连接
SELECT
    q1.total,
    q1.DOC_TYPE,
    q1.ACC_NUM,
    q1.CKAI,
    q1.COMPOUND,
    q1.TICKET,
    q1.P,
    NVL(q2.TOTAL_NUM_GROUP, 0) AS TOTAL_NUM_GROUP -- 空值处理为0
FROM (
    -- 原Query1的完整代码
    SELECT (
            SELECT COUNT(*)
            FROM (
                SELECT DISTINCT el.REFERENCE
                FROM T_LEJ el
                WHERE el.REFERENCE <> 'NUM'
                    AND el.REFERENCE <> 'J'
                    AND el.REFERENCE <> 'DEBIT'
                    AND el.REFERENCE <> 'CREDIT'
                    AND el.REFERENCE <> 'COMPOUND'
                    AND el.REFERENCE <> 'TICKET'
                    AND el.REFERENCE <> 'P'
                    AND el.REFERENCE NOT LIKE 'BBK%'
            )
        ) AS total,
        CASE
            WHEN c.DOC_TYPE = '1 RECEIPT' THEN 'RECEIPT'
            ELSE c.DOC_TYPE
        END AS DOC_TYPE,
        c.ACC_NUM,
        NVL(c.CKAI, 0) AS CKAI,
        NVL(c.COMPOUND, 0) AS COMPOUND,
        NVL(c.TICKET, 0) AS TICKET,
        NVL(c.P, 0) AS P
        
    FROM (
        SELECT
            *
        FROM (
            SELECT
                '1 RECEIPT' AS DOC_TYPE,
                COUNT(DISTINCT el.ACC_NUM) AS ACC_NUM,
                SUM(CASE WHEN el.REF_CODE = '61101' THEN NVL(SUM(el.CREDIT - el.DEBIT), 0) END) AS CKAI,
                SUM(CASE WHEN el.REF_CODE = '76101' THEN NVL(SUM(el.CREDIT - el.DEBIT), 0) END) AS COMPOUND,
                SUM(CASE WHEN el.REF_CODE = '76102' THEN NVL(SUM(CREDIT - DEBIT), 0) END) AS TICKET,
                SUM(CASE WHEN el.REF_CODE = '76103' THEN NVL(SUM(CREDIT - DEBIT), 0) END) AS P
            FROM T_LEJ el
            WHERE el.REFERENCE = 'RECEIPT'
            AND el.TRK_TRANS BETWEEN TO_DATE('01/01/2024', 'DD/MM/YYYY') AND TO_DATE('31/01/2024', 'DD/MM/YYYY')
            GROUP BY el.REF_CODE, el.CREDIT, el.DEBIT, el.ACC_NUM
        ) A
        UNION
        SELECT
            *
        FROM (
            SELECT
                SUBSTR(CONCAT(ra.SF, CONCAT(' | ', ra.DETAIL)), 0, 35) AS DOC_TYPE,
                COUNT(DISTINCT lej.ACC_NUM) AS ACC_NUM,
                NVL(SUM(lej.CKAI), 0) AS CKAI,
                NVL(SUM(lej.COMPOUND), 0) AS COMPOUND,
                NVL(SUM(lej.TICKET), 0) AS TICKET,
                NVL(SUM(lej.P), 0) AS P
            FROM (
                SELECT DISTINCT el.REFERENCE
                FROM T_LEJ el
                WHERE el.REFERENCE <> 'NUM'
                    AND el.REFERENCE <> 'J'
                    AND el.REFERENCE <> 'DEBIT'
                    AND el.REFERENCE <> 'CREDIT'
                    AND el.REFERENCE <> 'COMPOUND'
                    AND el.REFERENCE <> 'TICKET'
                    AND el.REFERENCE <> 'P'
                    AND el.REFERENCE <> 'RECEIPT'
                    AND el.REFERENCE NOT LIKE 'BBK%'
            ) ruj
            LEFT JOIN REF_AGENCY ra ON ruj.REFERENCE = ra.SF
            LEFT JOIN (
                SELECT
                    el.REFERENCE,
                    el.ACC_NUM,
                    CASE
                        WHEN el.REF_CODE = '61101' THEN NVL(SUM(CREDIT - DEBIT), 0)
                    END AS CKAI,
                    CASE
                        WHEN el.REF_CODE = '76101' THEN NVL(SUM(CREDIT - DEBIT), 0)
                    END AS COMPOUND,
                    CASE
                        WHEN el.REF_CODE = '76102' THEN NVL(SUM(CREDIT - DEBIT), 0)
                    END AS TICKET,
                    CASE
                        WHEN el.REF_CODE = '76103' THEN NVL(SUM(CREDIT - DEBIT), 0)
                    END AS P
                FROM REF_AGENCY ra
                LEFT JOIN T_LEJ el ON ra.SF = el.REFERENCE
                WHERE el.TRK_TRANS BETWEEN TO_DATE('01/01/2024', 'DD/MM/YYYY') AND TO_DATE('31/01/2024', 'DD/MM/YYYY')
                GROUP BY el.REFERENCE, el.CREDIT, el.DEBIT, el.REF_CODE, el.ACC_NUM
            ) lej ON ra.SF = lej.REFERENCE
            GROUP BY ra.SF, ra.DETAIL
        ) B
    ) C
) q1
LEFT JOIN (
    -- 原Query2的完整代码
    SELECT
        DOC_TYPE,
        SUM(e.NUM_GROUP) AS TOTAL_NUM_GROUP
    FROM (
        SELECT
            DISTINCT e.GROUP_ID,
            SUBSTR(CONCAT(ra.SF, CONCAT(' | ', ra.REFERENCE)), 0, 35) AS DOC_TYPE,
            e.NUM_GROUP
        FROM
            T_TRANSACTION e
        LEFT JOIN
            T_TRANSACTION_DET ed ON e.GROUP_ID = ed.GROUP_ID
        LEFT JOIN
            T_LEJ el ON ed.ACC_NUM = el.ACC_NUM
        LEFT JOIN
            REF_AGENCY ra ON el.REFERENCE = ra.SF
        WHERE
            e.COLL_CENTER = ra.SF
            AND 
            e.TRK_KLMPK >= TO_DATE('2024-01-01', 'YYYY-MM-DD')
            AND e.TRK_KLMPK <= TO_DATE('2024-01-31', 'YYYY-MM-DD')
    ) e
    GROUP BY
        DOC_TYPE
) q2 ON q1.DOC_TYPE = q2.DOC_TYPE
ORDER BY q1.DOC_TYPE;

说明

将Query1封装为子查询q1，Query2封装为子查询q2；
通过q1.DOC_TYPE = q2.DOC_TYPE进行左连接，保证q1的所有记录都被保留；
使用NVL(q2.TOTAL_NUM_GROUP, 0)将无匹配的TOTAL_NUM_GROUP值设为0，若不需要可直接使用q2.TOTAL_NUM_GROUP；
最终输出包含Query1的所有字段及Query2的TOTAL_NUM_GROUP字段，符合预期需求。

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

如何合并输出列不同的两个SQL查询（含Union的Query1与Query2）

问题概述 #

Query1 代码及输出 #

代码 #

输出说明 #

Query2 代码及输出 #

代码 #

输出说明 #

预期输出 #

解决方案：通过左连接合并结果 #

说明 #

问题概述

Query1 代码及输出

代码

输出说明

Query2 代码及输出

代码

输出说明

预期输出

解决方案：通过左连接合并结果

说明