如何在Oracle中实现单列双条件记录查询（适配多表关联低开销）

场景条件 #

• TABLE1中不存在flag为"A"的对应记录
• 目标loan_no在TABLE1中的所有记录flag必须为"D"，且该loan_no的最大dt晚于2023年3月1日（注：根据预期结果反推，原场景描述存在笔误，此处以实际预期结果逻辑为准）

TABLE1样本数据 #

loan_no  flag   amt    dt

913813    A     100   01-01-2023
913813    A     200   02-01-2023
913813    D     300   03-01-2023
813713    D     600   04-01-2023
813713    D     8700  05-01-2023
813713    D     560   06-01-2023
123678    D     150   07-01-2023
567345    P     789   08-01-2023
567345    D     789   09-01-2023
912353    D     454   10-09-2023

预期查询结果 #

loan_no  flag   amt    dt

813713    D     560   06-01-2023
123678    D     150   07-01-2023

要求 #

• 查询语句为SELECT子句形式，可关联另外三张表
• 需控制性能开销，避免过高

DDL与DML语句 #

create table TABLE1 (
loan_no varchar2(200),
flag varchar2(200),
amt varchar2(200),
dt  date
);
/
    
insert into table1 values ('913813','A','100',to_date('01-01-2023','dd-mm-yyyy'));
insert into table1 values ('913813','A','200',to_date('02-01-2023','dd-mm-yyyy'));
insert into table1 values ('913813','D','300',to_date('03-01-2023','dd-mm-yyyy'));
insert into table1 values ('813713','D','600',to_date('04-01-2023','dd-mm-yyyy'));
insert into table1 values ('813713','D','8700',to_date('05-01-2023','dd-mm-yyyy'));
insert into table1 values ('813713','D','560',to_date('06-01-2023','dd-mm-yyyy'));
insert into table1 values ('123678','D','150',to_date('07-01-2023','dd-mm-yyyy'));
insert into table1 values ('567345','P','789',to_date('08-01-2023','dd-mm-yyyy'));
insert into table1 values ('567345','D','789',to_date('09-01-2023','dd-mm-yyyy'));
insert into table1 values ('912353','D','454',to_date('10-01-2023','dd-mm-yyyy'));

优化后的查询语句 #

SELECT t1.loan_no, t1.flag, t1.amt, t1.dt
FROM TABLE1 t1
-- 示例关联其他三张表（假设表名为TABLE2、TABLE3、TABLE4，关联键为loan_no）
JOIN TABLE2 t2 ON t1.loan_no = t2.loan_no
JOIN TABLE3 t3 ON t1.loan_no = t3.loan_no
JOIN TABLE4 t4 ON t1.loan_no = t4.loan_no
WHERE 
    -- 排除存在flag='A'的loan_no
    t1.loan_no NOT IN (SELECT loan_no FROM TABLE1 WHERE flag = 'A')
    -- 排除存在非'D' flag的loan_no
    AND t1.loan_no NOT IN (SELECT loan_no FROM TABLE1 WHERE flag != 'D')
    -- 筛选当前loan_no的最新记录
    AND t1.dt = (SELECT MAX(dt) FROM TABLE1 WHERE loan_no = t1.loan_no)
    -- 最新记录的dt晚于2023年3月1日
    AND t1.dt > TO_DATE('01-03-2023', 'dd-mm-yyyy');

性能优化说明 #

1. 索引优化：在TABLE1上创建(loan_no, flag)和(loan_no, dt)复合索引，大幅提升子查询的执行效率
2. 过滤前置：将条件过滤放在WHERE子句前端，提前排除不符合条件的记录，减少后续关联和计算的数据量
3. 关联效率：确保关联表的loan_no字段存在索引，避免JOIN操作时的全表扫描

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