试试改进下算法: select * from ( SELECT CUS_LAST_NAME, CUS_MIDDLE_NAME,....., row_number() over(partition by cus_license_number,order by cus_address_type desc) rn WHERE lower(cus_last_name) like '%smith%' and cus_address_type in ('01','02) ) where rn = 1
改正下: select * from ( SELECT CUS_LAST_NAME, CUS_MIDDLE_NAME,....., row_number() over(partition by cus_license_number order by cus_address_type desc) rn WHERE lower(cus_last_name) like '%smith%' and cus_address_type in ('01','02) ) where rn = 1
lower(cus_last_name) like '%smith%' 应该可以改为cus_last_name like '%SMITH%'
cus_last_name like '%SMITH%' 也用不到索引 不如用instr,然后建一个函数索引
select * from (
SELECT CUS_LAST_NAME, CUS_MIDDLE_NAME,.....,
row_number() over(partition by cus_license_number,order by cus_address_type desc) rn
WHERE lower(cus_last_name) like '%smith%' and cus_address_type in ('01','02)
) where rn = 1
select * from (
SELECT CUS_LAST_NAME, CUS_MIDDLE_NAME,.....,
row_number() over(partition by cus_license_number order by cus_address_type desc) rn
WHERE lower(cus_last_name) like '%smith%' and cus_address_type in ('01','02)
) where rn = 1
应该可以改为cus_last_name like '%SMITH%'
也用不到索引
不如用instr,然后建一个函数索引