Oracle SQL基于VARCHAR2类型CODE列的规则筛选查询实现

环境说明 #

运行环境为Oracle数据库，共涉及3张测试表。

测试表结构 #

• TABLE1：仅含NUMBER(3)类型非空字段ROLL_NO

CREATE TABLE TABLE1 (ROLL_NO NUMBER(3) NOT NULL);

• TABLE2：含3个NUMBER(3)类型非空字段：ROLL_NO、CLASS、SEC

CREATE TABLE TABLE2 (ROLL_NO NUMBER(3) NOT NULL, CLASS NUMBER(3) NOT NULL, SEC NUMBER(3) NOT NULL);

• TABLE3：含3个非空字段：ROLL_NO NUMBER(3)、CODE VARCHAR2(3)、AMT NUMBER(3)

CREATE TABLE TABLE3 (ROLL_NO NUMBER(3) NOT NULL, CODE VARCHAR2(3) NOT NULL, AMT NUMBER(3) NOT NULL);

测试数据 #

INSERT INTO TABLE1 VALUES (101);
INSERT INTO TABLE1 VALUES (102);
INSERT INTO TABLE1 VALUES (103);
INSERT INTO TABLE1 VALUES (104);
----------------------------------
INSERT INTO TABLE2 VALUES (101,1, 12);
INSERT INTO TABLE2 VALUES (102,1, 15);
INSERT INTO TABLE3 VALUES (101, 'A2', 100);
INSERT INTO TABLE3 VALUES (101, '10', 200);
INSERT INTO TABLE3 VALUES (102, 'B3', 300);
INSERT INTO TABLE3 VALUES (102, '10', 400);
INSERT INTO TABLE3 VALUES (102, '19', 500);
INSERT INTO TABLE3 VALUES (103, '04', 600);
INSERT INTO TABLE3 VALUES (103, '98', 700);

初始查询与问题 #

初始查询逻辑为TABLE1内连接TABLE2、左连接TABLE3，无匹配TABLE3记录时通过NVL函数将CODE、AMT默认赋值为0，初始SQL如下：

SELECT T1.ROLL_NO, 
  T2.CLASS, 
  T2.SEC, 
  NVL(T3.CODE,0) AS CODE, 
  NVL(T3.AMT, 0) AS AMT
 FROM TABLE1 T1
 JOIN TABLE2 T2 ON T1.ROLL_NO = T2.ROLL_NO
 LEFT JOIN TABLE3 T3 ON T1.ROLL_NO = T3.ROLL_NO
 WHERE T1.ROLL_NO IN (101,102,103,104);

初始查询返回结果存在冗余，需调整逻辑满足以下筛选规则：

• 规则a：若某ROLL_NO下同时存在CODE='10'和其他CODE值（如A2、B3、01等）的记录，仅返回CODE='10'的对应行
• 规则b：若某ROLL_NO下不存在CODE='10'的记录，则返回该ROLL_NO下所有其他CODE值的记录
• 规则c：无匹配TABLE3记录的ROLL_NO，仍保留CODE、AMT为0的默认行
  预期返回结果如下：

ROLL_NO   CLASS   SEC    CODE    AMT
-------------------------------------
101       1      12     10      200
102       1      15     10      400
103       1      25     04      600
103       1      25     98      700
104       1      34      0        0 

解决方案 #

VARCHAR2类型字段不影响窗口函数使用，不需要强制类型转换，通过分组标记+自定义关联条件即可实现需求，可运行SQL如下：

WITH T3_MARK AS (
  SELECT
    ROLL_NO,
    CODE,
    AMT,
    -- 按学号分组，标记当前分组是否存在CODE='10'的记录
    MAX(CASE WHEN CODE = '10' THEN 1 ELSE 0 END) OVER (PARTITION BY ROLL_NO) AS HAS_CODE10
  FROM TABLE3
)
SELECT
  T1.ROLL_NO,
  T2.CLASS,
  T2.SEC,
  -- CODE为字符串类型，默认值用字符'0'避免隐式类型转换
  NVL(T3.CODE, '0') AS CODE,
  NVL(T3.AMT, 0) AS AMT
FROM TABLE1 T1
INNER JOIN TABLE2 T2
  ON T1.ROLL_NO = T2.ROLL_NO
LEFT JOIN T3_MARK T3
  ON T1.ROLL_NO = T3.ROLL_NO
  AND (
    -- 分组存在CODE='10'时，仅关联CODE='10'的行
    (T3.HAS_CODE10 = 1 AND T3.CODE = '10')
    -- 分组不存在CODE='10'时，关联所有行
    OR T3.HAS_CODE10 = 0
  )
WHERE T1.ROLL_NO IN (101, 102, 103, 104);

逻辑说明 #

1. 预处理TABLE3数据时，通过窗口函数一次扫描即可标记每个ROLL_NO下是否存在目标CODE值，避免重复子查询
2. 左连接时通过条件判断动态选择关联的记录，天然满足三类筛选规则
3. 修正了初始SQL中CODE字段NVL默认值的类型问题，避免Oracle隐式转换带来的查询异常或性能问题

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