如何在存储过程中输出两个查询结果用于SSRS报表？

核心思路 #

两个查询无法直接用UNION的原因是列数不一致——第一个查询包含COUNTRY列，第二个没有。解决方法是给总计查询补充一个固定的COUNTRY标识（比如'总计'），让两个查询的列数、列类型完全匹配，再用UNION ALL合并，最后封装为存储过程返回游标。

步骤1：调整查询，统一列结构 #

给两个查询的统计列添加明确别名，并给总计查询补充COUNTRY列：

分国家统计查询（调整后）

SELECT
    M.CDE_COUNTRY || '-' || M.DESC_COUNTRY AS COUNTRY,
    SUM(CASE WHEN RED_CODE = 'RD' THEN 1 ELSE 0 END) AS RED_COUNT,
    SUM(CASE WHEN BLUE_CODE = 'BE' THEN 1 ELSE 0 END) AS BLUE_COUNT,
    SUM(CASE WHEN NA_CODE_1 = 'NA' THEN 1 ELSE 0 END) AS NA1_COUNT,
    SUM(CASE WHEN PURPLE_CODE = 'PE' THEN 1 ELSE 0 END) AS PURPLE_COUNT,
    SUM(CASE WHEN GREEN_CODE = 'GN' THEN 1 ELSE 0 END) AS GREEN_COUNT,
    SUM(CASE WHEN YELLOW_CODE = 'YW' THEN 1 ELSE 0 END) AS YELLOW_COUNT,
    SUM(CASE WHEN NA_CODE_2 = 'NA' THEN 1 ELSE 0 END) AS NA2_COUNT
FROM 
    MASTER.T_COUNTRY M,
    PERSON.T_PERSON P,
    PERSON.T_CASE C
    -- 注意：若颜色字段来自COLORS_PER_COUNTRY表，需补充关联条件，例如：
    -- JOIN COLORS_PER_COUNTRY CP ON XXX = XXX
WHERE 
    TABLE_NAME IN ('H1')
    AND C.PERSON_ID = P.PERSON_ID
    AND M.CDE_COUNTRY = SUBSTR(LPAD(P.PERSON_ID,6,0),1,2)
GROUP BY 
    M.CDE_COUNTRY, M.DESC_COUNTRY

总计查询（调整后）

SELECT
    '总计' AS COUNTRY,
    SUM(CASE WHEN RED_CODE = 'RD' THEN 1 ELSE 0 END) AS RED_COUNT, -- 原查询用'R'，若为笔误请统一为'RD'，否则统计逻辑不一致
    SUM(CASE WHEN BLUE_CODE = 'BE' THEN 1 ELSE 0 END) AS BLUE_COUNT, -- 原查询用'B'，同理需确认是否为笔误
    SUM(CASE WHEN NA_CODE_1 = 'NA' THEN 1 ELSE 0 END) AS NA1_COUNT,
    SUM(CASE WHEN PURPLE_CODE = 'PE' THEN 1 ELSE 0 END) AS PURPLE_COUNT, -- 原查询用'P'，需确认
    SUM(CASE WHEN GREEN_CODE = 'GN' THEN 1 ELSE 0 END) AS GREEN_COUNT, -- 原查询用'G'，需确认
    SUM(CASE WHEN YELLOW_CODE = 'YW' THEN 1 ELSE 0 END) AS YELLOW_COUNT, -- 原查询用'Y'，需确认
    SUM(CASE WHEN NA_CODE_2 = 'NA' THEN 1 ELSE 0 END) AS NA2_COUNT
FROM 
    PERSON.T_PERSON P,
    PERSON.T_CASE C
    -- 若颜色字段来自COLORS_PER_COUNTRY表，需补充关联条件
WHERE 
    C.PERSON_ID = P.PERSON_ID

步骤2：合并查询并封装为存储过程 #

将两个查询用UNION ALL合并，封装为Oracle存储过程（适配VARCHAR2类型，符合你的数据库环境）：

CREATE OR REPLACE PROCEDURE SP_GET_COLOR_STATS(
    p_out_cursor OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_out_cursor FOR
        -- 分国家统计
        SELECT
            M.CDE_COUNTRY || '-' || M.DESC_COUNTRY AS COUNTRY,
            SUM(CASE WHEN RED_CODE = 'RD' THEN 1 ELSE 0 END) AS RED_COUNT,
            SUM(CASE WHEN BLUE_CODE = 'BE' THEN 1 ELSE 0 END) AS BLUE_COUNT,
            SUM(CASE WHEN NA_CODE_1 = 'NA' THEN 1 ELSE 0 END) AS NA1_COUNT,
            SUM(CASE WHEN PURPLE_CODE = 'PE' THEN 1 ELSE 0 END) AS PURPLE_COUNT,
            SUM(CASE WHEN GREEN_CODE = 'GN' THEN 1 ELSE 0 END) AS GREEN_COUNT,
            SUM(CASE WHEN YELLOW_CODE = 'YW' THEN 1 ELSE 0 END) AS YELLOW_COUNT,
            SUM(CASE WHEN NA_CODE_2 = 'NA' THEN 1 ELSE 0 END) AS NA2_COUNT
        FROM 
            MASTER.T_COUNTRY M,
            PERSON.T_PERSON P,
            PERSON.T_CASE C
            -- 补充COLORS_PER_COUNTRY关联（如果需要）
            -- JOIN COLORS_PER_COUNTRY CP ON CP.XXX = P.XXX OR CP.XXX = C.XXX
        WHERE 
            TABLE_NAME IN ('H1')
            AND C.PERSON_ID = P.PERSON_ID
            AND M.CDE_COUNTRY = SUBSTR(LPAD(P.PERSON_ID,6,0),1,2)
        GROUP BY 
            M.CDE_COUNTRY, M.DESC_COUNTRY
        UNION ALL
        -- 总计
        SELECT
            '总计' AS COUNTRY,
            SUM(CASE WHEN RED_CODE = 'RD' THEN 1 ELSE 0 END) AS RED_COUNT,
            SUM(CASE WHEN BLUE_CODE = 'BE' THEN 1 ELSE 0 END) AS BLUE_COUNT,
            SUM(CASE WHEN NA_CODE_1 = 'NA' THEN 1 ELSE 0 END) AS NA1_COUNT,
            SUM(CASE WHEN PURPLE_CODE = 'PE' THEN 1 ELSE 0 END) AS PURPLE_COUNT,
            SUM(CASE WHEN GREEN_CODE = 'GN' THEN 1 ELSE 0 END) AS GREEN_COUNT,
            SUM(CASE WHEN YELLOW_CODE = 'YW' THEN 1 ELSE 0 END) AS YELLOW_COUNT,
            SUM(CASE WHEN NA_CODE_2 = 'NA' THEN 1 ELSE 0 END) AS NA2_COUNT
        FROM 
            PERSON.T_PERSON P,
            PERSON.T_CASE C
            -- 补充COLORS_PER_COUNTRY关联（如果需要）
        WHERE 
            C.PERSON_ID = P.PERSON_ID;
END SP_GET_COLOR_STATS;
/

注意事项 #

• 颜色代码匹配问题：原总计查询中使用的代码（如'R'、'B'）与分国家查询的'RD'、'BE'不一致，需确认是否为笔误，否则统计结果会出现偏差。
• 表关联问题：若颜色字段（RED_CODE等）来自COLORS_PER_COUNTRY表，需在两个查询中补充该表与T_PERSON或T_CASE的关联条件，否则会出现笛卡尔积导致统计错误。
• SSRS适配：存储过程输出的游标可以直接作为SSRS报表的数据源，报表中可通过COUNTRY列区分分国家数据和总计行。

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