SQL多结果联动：如何将代码-组名配对结果作为二次查询输入生成报表

核心问题说明 #

单值筛选时使用IN子查询的逻辑，在需要匹配两个字段组合配对的场景下，不能拆成两个独立的IN条件——拆分会导致跨组合错配（比如原配对是A-code配X-group、B-code配Y-group，拆分匹配会错误识别A配Y、B配X的无效组合），工程上有三种通用的无错实现方案：

• 基于CTE的关联匹配方案：逻辑清晰、易维护，适配绝大多数SQL场景
• 基于EXISTS的存在性匹配方案：兼容性强，适合老旧SQL版本
• 基于行构造的多值IN匹配方案：写法简洁，适配现代SQL引擎

方案1：CTE关联匹配（推荐，可读性最优） #

直接将最初获取cc.code + ii.ii_groupname配对清单的逻辑抽为公共CTE，再关联统计阶段的事实表做聚合，全程不需要重复写过滤条件，逻辑可追溯：

WITH valid_pairs AS (
    -- 替换为最初的配对查询逻辑，保持原有的表关联、筛选条件不变
    SELECT DISTINCT cc.code, ii.ii_groupname
    FROM ds
    LEFT OUTER JOIN dd ON ds.关联键 = dd.关联键
    LEFT OUTER JOIN ii ON dd.关联键 = ii.关联键
    LEFT OUTER JOIN dcc ON dd.关联键 = dcc.关联键
    LEFT OUTER JOIN cc ON dcc.关联键 = cc.关联键
    LEFT OUTER JOIN bil ON ds.关联键 = bil.关联键
    LEFT OUTER JOIN cs ON bil.关联键 = cs.关联键
    WHERE
        cs.status = 'Pending'
        AND dd.date_of_service BETWEEN '2017/01/01' AND '2018/08/31'
        AND dcc.r_code IN ('42', '36')
)
-- 批量计算每对组合的统计值
SELECT
    p.code,
    p.ii_groupname,
    CAST(AVG(dcc.amount_allowed) AS decimal(10,2)) AS avg_allowed,
    CAST(AVG(dcc.paid_amount) AS decimal(10,2)) AS avg_paid
FROM valid_pairs p
-- 关联统计阶段的事实表，和原表关联逻辑保持一致
JOIN dd ON dd.关联键 = 对应匹配键
LEFT OUTER JOIN dcc ON dd.关联键 = dcc.关联键
LEFT OUTER JOIN cc ON dcc.关联键 = cc.关联键 AND cc.code = p.code
LEFT OUTER JOIN ii ON dd.关联键 = ii.关联键 AND ii.ii_groupname = p.ii_groupname
WHERE
    -- 应用统计阶段的专属筛选条件
    dd.date_of_service BETWEEN '2017/01/01' AND '2019/08/31'
    AND dcc.r_code IN ('42', '36')
    AND dcc.amount_allowed > 0
GROUP BY p.code, p.ii_groupname

方案2：EXISTS存在性匹配（兼容性最优） #

如果SQL版本不支持CTE、或者需要减少冗余的表关联操作，可以在统计查询的WHERE子句中，用EXISTS精准匹配原配对组合，完全避免跨组合错配：

SELECT
    cc.code,
    ii.ii_groupname,
    CAST(AVG(dcc.amount_allowed) AS decimal(10,2)) AS avg_allowed,
    CAST(AVG(dcc.paid_amount) AS decimal(10,2)) AS avg_paid
FROM dd
LEFT OUTER JOIN dcc ON dd.关联键 = dcc.关联键
LEFT OUTER JOIN cc ON dcc.关联键 = cc.关联键
LEFT OUTER JOIN ii ON dd.关联键 = ii.关联键
WHERE
    dd.date_of_service BETWEEN '2017/01/01' AND '2019/08/31'
    AND dcc.r_code IN ('42', '36')
    AND dcc.amount_allowed > 0
    -- 精准匹配原配对的组合，无错配
    AND EXISTS (
        SELECT 1
        FROM (
            -- 内嵌最初的配对查询逻辑
            SELECT DISTINCT cc_inner.code, ii_inner.ii_groupname
            FROM ds
            LEFT OUTER JOIN dd AS dd_inner ON ds.关联键 = dd_inner.关联键
            LEFT OUTER JOIN ii AS ii_inner ON dd_inner.关联键 = ii_inner.关联键
            LEFT OUTER JOIN dcc AS dcc_inner ON dd_inner.关联键 = dcc_inner.关联键
            LEFT OUTER JOIN cc AS cc_inner ON dcc_inner.关联键 = cc_inner.关联键
            LEFT OUTER JOIN bil ON ds.关联键 = bil.关联键
            LEFT OUTER JOIN cs ON bil.关联键 = cs.关联键
            WHERE
                cs.status = 'Pending'
                AND dd_inner.date_of_service BETWEEN '2017/01/01' AND '2018/08/31'
                AND dcc_inner.r_code IN ('42', '36')
        ) source_pairs
        WHERE source_pairs.code = cc.code
            AND source_pairs.ii_groupname = ii.ii_groupname
    )
GROUP BY cc.code, ii.ii_groupname

方案3：行构造多值IN匹配（写法最简洁） #

对于支持现代SQL语法的引擎（MySQL 8.0+、PostgreSQL、Oracle等），可以直接用行构造的方式同时匹配两个字段的配对组合，写法和单值IN逻辑高度相似：

SELECT
    cc.code,
    ii.ii_groupname,
    CAST(AVG(dcc.amount_allowed) AS decimal(10,2)) AS avg_allowed,
    CAST(AVG(dcc.paid_amount) AS decimal(10,2)) AS avg_paid
FROM dd
LEFT OUTER JOIN dcc ON dd.关联键 = dcc.关联键
LEFT OUTER JOIN cc ON dcc.关联键 = cc.关联键
LEFT OUTER JOIN ii ON dd.关联键 = ii.关联键
WHERE
    dd.date_of_service BETWEEN '2017/01/01' AND '2019/08/31'
    AND dcc.r_code IN ('42', '36')
    AND dcc.amount_allowed > 0
    -- 直接匹配两个字段的配对组合，无错配
    AND (cc.code, ii.ii_groupname) IN (
        SELECT DISTINCT cc_inner.code, ii_inner.ii_groupname
        FROM ds
        LEFT OUTER JOIN dd AS dd_inner ON ds.关联键 = dd_inner.关联键
        LEFT OUTER JOIN ii AS ii_inner ON dd_inner.关联键 = ii_inner.关联键
        LEFT OUTER JOIN dcc AS dcc_inner ON dd_inner.关联键 = dcc_inner.关联键
        LEFT OUTER JOIN cc AS cc_inner ON dcc_inner.关联键 = cc_inner.关联键
        LEFT OUTER JOIN bil ON ds.关联键 = bil.关联键
        LEFT OUTER JOIN cs ON bil.关联键 = cs.关联键
        WHERE
            cs.status = 'Pending'
            AND dd_inner.date_of_service BETWEEN '2017/01/01' AND '2018/08/31'
            AND dcc_inner.r_code IN ('42', '36')
    )
GROUP BY cc.code, ii.ii_groupname

关键避坑提示 #

• 不要采用cc.code IN (SELECT code FROM 配对查询) AND ii.ii_groupname IN (SELECT group FROM 配对查询)的写法，这种写法会解耦两个字段的绑定关系，引入大量无效组合，导致统计结果完全失真
• 所有方案的核心逻辑都是保持两个字段的配对绑定关系，不拆分解耦匹配条件

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