如何横向合并两个SQL查询结果并添加行列汇总？

针对你的需求——横向合并两个查询结果、添加行级总计列和列级汇总行，以下是几种替代方案：

方法1：使用ROLLUP生成列级汇总 + 行级计算列 #

如果你的SQL引擎支持ROLLUP（如Snowflake、BigQuery、PostgreSQL 11+），可以先关联两个查询的结果，再用ROLLUP自动生成汇总行，同时直接计算行级总计：

WITH combined_data AS (
    -- 关联两个查询的结果
    SELECT
        q1.CUST_TYPE,
        q1.SCV_COMPENSATABLE_BALANCE,
        q2.BENEFICIARY_FUNDS,
        q2.FUNDS_UNDER_DISPUTE,
        q2.DORMANT_ACCOUNTS_X_20PC,
        -- 计算行级总计列
        q1.SCV_COMPENSATABLE_BALANCE + q2.BENEFICIARY_FUNDS + q2.FUNDS_UNDER_DISPUTE + q2.DORMANT_ACCOUNTS_X_20PC AS "TOTAL FSCS Compensation Limit Utilization"
    FROM (
        -- 简化后的查询1：去重后按CUST_TYPE求和
        SELECT
            CUST_TYPE,
            SUM(CUST_COMPENSATABLE_AMT) AS SCV_COMPENSATABLE_BALANCE
        FROM (
            SELECT
                CUST_TYPE,
                FSA_UNIQUE_REF_NO,
                CUST_COMPENSATABLE_AMT,
                ROW_NUMBER() OVER (PARTITION BY CUST_TYPE, FSA_UNIQUE_REF_NO ORDER BY CUST_TYPE) AS rank1
            FROM ddewd10s.FSCS_LIMIT_UTIL_SCV
            QUALIFY rank1 = 1
        ) A
        GROUP BY CUST_TYPE
    ) q1
    FULL JOIN (
        -- 查询2保持原样
        SELECT
            CUST_TYPE,
            Sum(CUST_BENEFICIARY_FUNDS) AS BENEFICIARY_FUNDS,
            Sum(CUST_FNDS_UNDR_DISPT_AMT) AS FUNDS_UNDER_DISPUTE,
            Sum(TWENTY_PCT_CUST_DOR_AMT) AS DORMANT_ACCOUNTS_X_20PC
        FROM ddewd10s.FSCS_LIMIT_UTIL
        WHERE DUPLICATE_URN = ''
        GROUP BY CUST_TYPE
    ) q2 ON q1.CUST_TYPE = q2.CUST_TYPE
)
-- 用ROLLUP生成汇总行
SELECT
    COALESCE(CUST_TYPE, 'TOTAL') AS CUST_TYPE,
    SUM(SCV_COMPENSATABLE_BALANCE) AS SCV_COMPENSATABLE_BALANCE,
    SUM(BENEFICIARY_FUNDS) AS BENEFICIARY_FUNDS,
    SUM(FUNDS_UNDER_DISPUTE) AS FUNDS_UNDER_DISPUTE,
    SUM(DORMANT_ACCOUNTS_X_20PC) AS DORMANT_ACCOUNTS_X_20PC,
    SUM("TOTAL FSCS Compensation Limit Utilization") AS "TOTAL FSCS Compensation Limit Utilization"
FROM combined_data
GROUP BY ROLLUP(CUST_TYPE)
ORDER BY CASE WHEN CUST_TYPE = 'TOTAL' THEN 1 ELSE 0 END, CUST_TYPE;

优势：利用SQL内置的ROLLUP语法，避免手动拼接汇总行，代码更简洁易维护。

方法2：CTE封装基础数据 + 手动添加汇总行 #

如果你的SQL引擎不支持ROLLUP，可以用CTE先处理基础关联数据，再单独计算汇总行，最后用UNION ALL合并：

WITH combined_data AS (
    SELECT
        q1.CUST_TYPE,
        q1.SCV_COMPENSATABLE_BALANCE,
        q2.BENEFICIARY_FUNDS,
        q2.FUNDS_UNDER_DISPUTE,
        q2.DORMANT_ACCOUNTS_X_20PC,
        q1.SCV_COMPENSATABLE_BALANCE + q2.BENEFICIARY_FUNDS + q2.FUNDS_UNDER_DISPUTE + q2.DORMANT_ACCOUNTS_X_20PC AS "TOTAL FSCS Compensation Limit Utilization"
    FROM (
        SELECT
            CUST_TYPE,
            SUM(CUST_COMPENSATABLE_AMT) AS SCV_COMPENSATABLE_BALANCE
        FROM (
            SELECT
                CUST_TYPE,
                FSA_UNIQUE_REF_NO,
                CUST_COMPENSATABLE_AMT,
                ROW_NUMBER() OVER (PARTITION BY CUST_TYPE, FSA_UNIQUE_REF_NO ORDER BY CUST_TYPE) AS rank1
            FROM ddewd10s.FSCS_LIMIT_UTIL_SCV
            QUALIFY rank1 = 1
        ) A
        GROUP BY CUST_TYPE
    ) q1
    FULL JOIN (
        SELECT
            CUST_TYPE,
            Sum(CUST_BENEFICIARY_FUNDS) AS BENEFICIARY_FUNDS,
            Sum(CUST_FNDS_UNDR_DISPT_AMT) AS FUNDS_UNDER_DISPUTE,
            Sum(TWENTY_PCT_CUST_DOR_AMT) AS DORMANT_ACCOUNTS_X_20PC
        FROM ddewd10s.FSCS_LIMIT_UTIL
        WHERE DUPLICATE_URN = ''
        GROUP BY CUST_TYPE
    ) q2 ON q1.CUST_TYPE = q2.CUST_TYPE
),
summary_row AS (
    SELECT
        'TOTAL' AS CUST_TYPE,
        SUM(SCV_COMPENSATABLE_BALANCE) AS SCV_COMPENSATABLE_BALANCE,
        SUM(BENEFICIARY_FUNDS) AS BENEFICIARY_FUNDS,
        SUM(FUNDS_UNDER_DISPUTE) AS FUNDS_UNDER_DISPUTE,
        SUM(DORMANT_ACCOUNTS_X_20PC) AS DORMANT_ACCOUNTS_X_20PC,
        SUM("TOTAL FSCS Compensation Limit Utilization") AS "TOTAL FSCS Compensation Limit Utilization"
    FROM combined_data
)
SELECT * FROM combined_data
UNION ALL
SELECT * FROM summary_row
ORDER BY CASE WHEN CUST_TYPE = 'TOTAL' THEN 1 ELSE 0 END, CUST_TYPE;

优势：兼容性极强，几乎所有SQL引擎都支持，逻辑清晰，便于自定义汇总规则。

方法3：子查询嵌套 + 窗口函数生成汇总列 #

可以用窗口函数一次计算全局汇总值，再将汇总值作为单独行输出，同时保留行级总计：

SELECT
    CUST_TYPE,
    SCV_COMPENSATABLE_BALANCE,
    BENEFICIARY_FUNDS,
    FUNDS_UNDER_DISPUTE,
    DORMANT_ACCOUNTS_X_20PC,
    "TOTAL FSCS Compensation Limit Utilization"
FROM (
    SELECT
        q1.CUST_TYPE,
        q1.SCV_COMPENSATABLE_BALANCE,
        q2.BENEFICIARY_FUNDS,
        q2.FUNDS_UNDER_DISPUTE,
        q2.DORMANT_ACCOUNTS_X_20PC,
        q1.SCV_COMPENSATABLE_BALANCE + q2.BENEFICIARY_FUNDS + q2.FUNDS_UNDER_DISPUTE + q2.DORMANT_ACCOUNTS_X_20PC AS "TOTAL FSCS Compensation Limit Utilization"
    FROM (
        SELECT
            CUST_TYPE,
            SUM(CUST_COMPENSATABLE_AMT) AS SCV_COMPENSATABLE_BALANCE
        FROM (
            SELECT
                CUST_TYPE,
                FSA_UNIQUE_REF_NO,
                CUST_COMPENSATABLE_AMT,
                ROW_NUMBER() OVER (PARTITION BY CUST_TYPE, FSA_UNIQUE_REF_NO ORDER BY CUST_TYPE) AS rank1
            FROM ddewd10s.FSCS_LIMIT_UTIL_SCV
            QUALIFY rank1 = 1
        ) A
        GROUP BY CUST_TYPE
    ) q1
    FULL JOIN (
        SELECT
            CUST_TYPE,
            Sum(CUST_BENEFICIARY_FUNDS) AS BENEFICIARY_FUNDS,
            Sum(CUST_FNDS_UNDR_DISPT_AMT) AS FUNDS_UNDER_DISPUTE,
            Sum(TWENTY_PCT_CUST_DOR_AMT) AS DORMANT_ACCOUNTS_X_20PC
        FROM ddewd10s.FSCS_LIMIT_UTIL
        WHERE DUPLICATE_URN = ''
        GROUP BY CUST_TYPE
    ) q2 ON q1.CUST_TYPE = q2.CUST_TYPE
) base
-- 合并原始行和汇总行
UNION ALL
SELECT
    'TOTAL' AS CUST_TYPE,
    SUM(SCV_COMPENSATABLE_BALANCE),
    SUM(BENEFICIARY_FUNDS),
    SUM(FUNDS_UNDER_DISPUTE),
    SUM(DORMANT_ACCOUNTS_X_20PC),
    SUM("TOTAL FSCS Compensation Limit Utilization")
FROM (
    SELECT
        q1.CUST_TYPE,
        q1.SCV_COMPENSATABLE_BALANCE,
        q2.BENEFICIARY_FUNDS,
        q2.FUNDS_UNDER_DISPUTE,
        q2.DORMANT_ACCOUNTS_X_20PC,
        q1.SCV_COMPENSATABLE_BALANCE + q2.BENEFICIARY_FUNDS + q2.FUNDS_UNDER_DISPUTE + q2.DORMANT_ACCOUNTS_X_20PC AS "TOTAL FSCS Compensation Limit Utilization"
    FROM (
        SELECT
            CUST_TYPE,
            SUM(CUST_COMPENSATABLE_AMT) AS SCV_COMPENSATABLE_BALANCE
        FROM (
            SELECT
                CUST_TYPE,
                FSA_UNIQUE_REF_NO,
                CUST_COMPENSATABLE_AMT,
                ROW_NUMBER() OVER (PARTITION BY CUST_TYPE, FSA_UNIQUE_REF_NO ORDER BY CUST_TYPE) AS rank1
            FROM ddewd10s.FSCS_LIMIT_UTIL_SCV
            QUALIFY rank1 = 1
        ) A
        GROUP BY CUST_TYPE
    ) q1
    FULL JOIN (
        SELECT
            CUST_TYPE,
            Sum(CUST_BENEFICIARY_FUNDS) AS BENEFICIARY_FUNDS,
            Sum(CUST_FNDS_UNDR_DISPT_AMT) AS FUNDS_UNDER_DISPUTE,
            Sum(TWENTY_PCT_CUST_DOR_AMT) AS DORMANT_ACCOUNTS_X_20PC
        FROM ddewd10s.FSCS_LIMIT_UTIL
        WHERE DUPLICATE_URN = ''
        GROUP BY CUST_TYPE
    ) q2 ON q1.CUST_TYPE = q2.CUST_TYPE
) summary
ORDER BY CASE WHEN CUST_TYPE = 'TOTAL' THEN 1 ELSE 0 END, CUST_TYPE;

优势：通过窗口函数一次性计算所有汇总值，避免重复关联基础表，适合数据量较大的场景。

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