复杂关联/过滤场景下的高效SQL实现方案咨询

你的原查询因为多次LEFT JOIN会生成大量中间数据集，在大表场景下性能拉胯，以下是更简洁高效的实现方式：

方案一：EXISTS子查询（优先推荐） #

EXISTS的逻辑是找到匹配项就停止检查，不会像JOIN那样产生冗余数据，性能更高效：

CREATE TABLE RELEVANT_ENTRIES AS
SELECT T1.*
FROM ENTRIES T1
WHERE 
  -- 验证至少一个A代码匹配对应子类的关联A代码
  EXISTS (
    SELECT 1
    FROM REL_A_CODES ra
    WHERE ra.SUBCLASS = T1.SUBCLASS
      AND ra.REL_A_CODE IN (T1.A_CODE_1, T1.A_CODE_2, T1.A_CODE_3)
  )
  AND
  -- 验证至少一个B代码匹配对应子类的关联B代码
  EXISTS (
    SELECT 1
    FROM REL_B_CODES rb
    WHERE rb.SUBCLASS = T1.SUBCLASS
      AND rb.REL_B_CODE IN (T1.B_CODE_1, T1.B_CODE_2, T1.B_CODE_3)
  );

方案二：UNPIVOT扁平化列（适合支持该语法的数据库） #

把ENTRIES里的多列A/B代码转成行数据，再和关联表关联，避免重复JOIN：

-- 以Oracle/SQL Server为例
CREATE TABLE RELEVANT_ENTRIES AS
WITH unpivoted_data AS (
  -- 筛选匹配A代码的条目
  SELECT 
    ENTRY_ID, SUBCLASS,
    A_CODE_1, A_CODE_2, A_CODE_3,
    B_CODE_1, B_CODE_2, B_CODE_3
  FROM ENTRIES
  JOIN REL_A_CODES ra 
    ON ra.SUBCLASS = ENTRIES.SUBCLASS
    AND ra.REL_A_CODE IN (A_CODE_1, A_CODE_2, A_CODE_3)
  UNION ALL
  -- 筛选匹配B代码的条目
  SELECT 
    ENTRY_ID, SUBCLASS,
    A_CODE_1, A_CODE_2, A_CODE_3,
    B_CODE_1, B_CODE_2, B_CODE_3
  FROM ENTRIES
  JOIN REL_B_CODES rb 
    ON rb.SUBCLASS = ENTRIES.SUBCLASS
    AND rb.REL_B_CODE IN (B_CODE_1, B_CODE_2, B_CODE_3)
)
SELECT DISTINCT 
  ENTRY_ID, SUBCLASS,
  A_CODE_1, A_CODE_2, A_CODE_3,
  B_CODE_1, B_CODE_2, B_CODE_3
FROM unpivoted_data
GROUP BY 
  ENTRY_ID, SUBCLASS,
  A_CODE_1, A_CODE_2, A_CODE_3,
  B_CODE_1, B_CODE_2, B_CODE_3
HAVING COUNT(*) >= 2; -- 同时存在A、B代码匹配的条目

额外性能提升建议 #

• 给REL_A_CODES建复合索引：CREATE INDEX idx_rel_a_subclass_code ON REL_A_CODES(SUBCLASS, REL_A_CODE);
• 给REL_B_CODES建复合索引：CREATE INDEX idx_rel_b_subclass_code ON REL_B_CODES(SUBCLASS, REL_B_CODE);
• 如果ENTRIES表的SUBCLASS字段过滤性强，也可以给它加个单独索引

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