SQL单表列关联多列的最优实现方式咨询

你当前用OR关联多列的方式不是最优方案，主要存在两个核心问题：

1. 性能瓶颈：多数数据库优化器对OR关联的支持有限，无法有效利用TRN_BILLING_HEAD表中BLH_INS1_ID、BLH_INS2_ID、BLH_INS3_ID的索引，容易触发全表扫描或低效的嵌套循环，查询速度慢。
2. 逻辑失真：如果一条TRN_BILLING_HEAD记录的多个INS列（比如BLH_INS1_ID和BLH_INS2_ID）都匹配同一个IM_ID，INNER JOIN会将这条记录重复关联，导致最终的COUNT(1)和各类SUM值被重复计算，结果不准确。

下面提供几种更优的方案，同时解决性能和逻辑问题：

方案1：用IN子句简化关联（仅优化写法，仍需注意重复行） #

将OR条件替换为IN子句，写法更简洁，部分数据库优化器能更好地处理这种等值匹配：

SELECT GR_NAME,
       COUNT(1),
       SUM(ERD_PAID_INS_AMT * ERD_FACTOR),
       SUM(ERD_INS_ADJUST_AMT * ERD_FACTOR),
       SUM(ERD_INS_WRITEOFF_AMT * ERD_FACTOR)
FROM TRN_ERA_HEAD
     INNER JOIN TRN_ERA_DET ON ERD_ERH_ID = ERH_ID
     INNER JOIN TRN_BILLING_HEAD ON ERD_BLH_ID = BLH_ID
     INNER JOIN TRN_BILLING_DET ON ERD_BLD_ID = BLD_ID
     INNER JOIN MST_INSURANCE ON IM_ID IN (BLH_INS1_ID, BLH_INS2_ID, BLH_INS3_ID)
     INNER JOIN MST_GROUPS ON IM_ARGRP_ID = GR_ID
WHERE ERH_TRNTYPE IN ('IN', 'IC')
  AND ERH_BOOL_INACTIVE = 0
  AND ERH_STATUS = 'P'
  AND ERH_DOC_DATE >= @P0
  AND ERH_DOC_DATE <= @P1
GROUP BY GR_NAME
ORDER BY GR_NAME;

⚠️ 注意：此方案仍未解决重复行导致的聚合结果失真问题，仅优化了写法和部分性能。

方案2：修正UNION ALL实现（解决逻辑和性能问题） #

你之前的UNION ALL写法错误在于每个分支单独聚合后再合并，导致同一GR_NAME的结果分散且无法正确累加。正确的做法是先通过UNION ALL获取所有符合条件的明细行，再统一聚合：

WITH base_records AS (
    -- 匹配BLH_INS1_ID的明细
    SELECT 
        GR_NAME,
        ERD_PAID_INS_AMT * ERD_FACTOR AS paid_amt,
        ERD_INS_ADJUST_AMT * ERD_FACTOR AS adjust_amt,
        ERD_INS_WRITEOFF_AMT * ERD_FACTOR AS writeoff_amt
    FROM TRN_ERA_HEAD
         INNER JOIN TRN_ERA_DET ON ERD_ERH_ID = ERH_ID
         INNER JOIN TRN_BILLING_HEAD ON ERD_BLH_ID = BLH_ID
         INNER JOIN TRN_BILLING_DET ON ERD_BLD_ID = BLD_ID
         INNER JOIN MST_INSURANCE ON BLH_INS1_ID = IM_ID
         INNER JOIN MST_GROUPS ON IM_ARGRP_ID = GR_ID
    WHERE ERH_TRNTYPE IN ('IN', 'IC')
      AND ERH_BOOL_INACTIVE = 0
      AND ERH_STATUS = 'P'
      AND ERH_DOC_DATE >= @P0
      AND ERH_DOC_DATE <= @P1

    UNION ALL

    -- 匹配BLH_INS2_ID的明细
    SELECT 
        GR_NAME,
        ERD_PAID_INS_AMT * ERD_FACTOR,
        ERD_INS_ADJUST_AMT * ERD_FACTOR,
        ERD_INS_WRITEOFF_AMT * ERD_FACTOR
    FROM TRN_ERA_HEAD
         INNER JOIN TRN_ERA_DET ON ERD_ERH_ID = ERH_ID
         INNER JOIN TRN_BILLING_HEAD ON ERD_BLH_ID = BLH_ID
         INNER JOIN TRN_BILLING_DET ON ERD_BLD_ID = BLD_ID
         INNER JOIN MST_INSURANCE ON BLH_INS2_ID = IM_ID
         INNER JOIN MST_GROUPS ON IM_ARGRP_ID = GR_ID
    WHERE ERH_TRNTYPE IN ('IN', 'IC')
      AND ERH_BOOL_INACTIVE = 0
      AND ERH_STATUS = 'P'
      AND ERH_DOC_DATE >= @P0
      AND ERH_DOC_DATE <= @P1

    UNION ALL

    -- 匹配BLH_INS3_ID的明细
    SELECT 
        GR_NAME,
        ERD_PAID_INS_AMT * ERD_FACTOR,
        ERD_INS_ADJUST_AMT * ERD_FACTOR,
        ERD_INS_WRITEOFF_AMT * ERD_FACTOR
    FROM TRN_ERA_HEAD
         INNER JOIN TRN_ERA_DET ON ERD_ERH_ID = ERH_ID
         INNER JOIN TRN_BILLING_HEAD ON ERD_BLH_ID = BLH_ID
         INNER JOIN TRN_BILLING_DET ON ERD_BLD_ID = BLD_ID
         INNER JOIN MST_INSURANCE ON BLH_INS3_ID = IM_ID
         INNER JOIN MST_GROUPS ON IM_ARGRP_ID = GR_ID
    WHERE ERH_TRNTYPE IN ('IN', 'IC')
      AND ERH_BOOL_INACTIVE = 0
      AND ERH_STATUS = 'P'
      AND ERH_DOC_DATE >= @P0
      AND ERH_DOC_DATE <= @P1
)
-- 统一聚合所有明细
SELECT 
    GR_NAME,
    COUNT(1),
    SUM(paid_amt),
    SUM(adjust_amt),
    SUM(writeoff_amt)
FROM base_records
GROUP BY GR_NAME
ORDER BY GR_NAME;

此方案避免了重复行导致的聚合错误，且每个分支的等值关联能有效利用索引，性能优于原OR写法。

方案3：用UNPIVOT转换列行为行（最优性能方案，需数据库支持） #

如果你的数据库支持UNPIVOT（如SQL Server、Oracle），可以将TRN_BILLING_HEAD的三个INS列转换为行数据，再进行关联，只需扫描一次主表，性能最优：

SELECT 
    GR_NAME,
    COUNT(1),
    SUM(ERD_PAID_INS_AMT * ERD_FACTOR),
    SUM(ERD_INS_ADJUST_AMT * ERD_FACTOR),
    SUM(ERD_INS_WRITEOFF_AMT * ERD_FACTOR)
FROM TRN_ERA_HEAD
     INNER JOIN TRN_ERA_DET ON ERD_ERH_ID = ERH_ID
     INNER JOIN TRN_BILLING_HEAD ON ERD_BLH_ID = BLH_ID
     INNER JOIN TRN_BILLING_DET ON ERD_BLD_ID = BLD_ID
     -- 将三个INS列转成行，排除空值避免无效关联
     INNER JOIN (
         SELECT BLH_ID, ins_id
         FROM TRN_BILLING_HEAD
         UNPIVOT (
             ins_id FOR ins_col IN (BLH_INS1_ID, BLH_INS2_ID, BLH_INS3_ID)
         ) AS unpivoted
         WHERE ins_id IS NOT NULL
     ) AS bh_ins ON TRN_BILLING_HEAD.BLH_ID = bh_ins.BLH_ID
     INNER JOIN MST_INSURANCE ON bh_ins.ins_id = IM_ID
     INNER JOIN MST_GROUPS ON IM_ARGRP_ID = GR_ID
WHERE ERH_TRNTYPE IN ('IN', 'IC')
  AND ERH_BOOL_INACTIVE = 0
  AND ERH_STATUS = 'P'
  AND ERH_DOC_DATE >= @P0
  AND ERH_DOC_DATE <= @P1
GROUP BY GR_NAME
ORDER BY GR_NAME;

这种写法将多列转成行后，用单一等值条件关联，完全利用索引，且避免了重复扫描主表，是性能最优的方案。

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