原sql:
select max(id) AS id from (
SELECT C_LOANACCOUNTCODE, c_projectcode,MAX(ID) as id,C_DEPCODE,c_risk_level,c_projectname,C_LOANACCOUNTNAME,c_bistype FROM trisk_apply_his
inner join (
select REGEXP_SUBSTR(?, '[^,]+', 1, LEVEL, 'i') AS ADDUSERCD
from DUAL
CONNECT BY LEVEL <= LENGTH(?) - LENGTH(replace(?, ',', '')) + 1
)on instr(',' || c_depcode || ',', ',' || ADDUSERCD || ',') > 0
WHERE trisk_apply_his.c_flow_status = 2 AND D_FLOW_FINISH_DATE < to_date('2023-05-23','yyyy-mm-dd')+1
group by C_LOANACCOUNTCODE, c_projectcode,C_DEPCODE,c_risk_level,c_projectname,C_LOANACCOUNTNAME,c_bistype
)
group by c_projectcode,C_LOANACCOUNTCODE
优化后的代码:
SELECT
max(id) id
from (
select C_LOANACCOUNTCODE, c_projectcode,c_depcode,id from (
SELECT C_LOANACCOUNTCODE, c_projectcode,c_depcode,id ,row_number() over(partition by ID order by C_LOANACCOUNTCODE, c_projectcode ,c_depcode desc) rn FROM trisk_apply_his
WHERE trisk_apply_his.c_flow_status = 2 AND D_FLOW_FINISH_DATE < to_date(?,'yyyy-mm-dd')+1
) where rn = 1
) trisk_apply_his where exists (
select * from (
select REGEXP_SUBSTR(?, '[^,]+', 1, LEVEL, 'i') AS ADDUSERCD
from DUAL
CONNECT BY LEVEL <= LENGTH(?) - LENGTH(replace(?, ',', '')) + 1
) dep where instr(',' || trisk_apply_his.c_depcode || ',', ',' || dep.ADDUSERCD || ',') > 0
) group by C_LOANACCOUNTCODE, c_projectcode
减少统计函数的使用可以优化效率