1.OLD:

SELECTcard.c_cust_id,card.TYPE,card.n_all_moneyFROMcardWHEREcard.c_cust_idNOTIN(SELECTc_cust_idFROMcardWHERETYPEIN('11','12','13','14')ANDflag='1')ANDcard.TYPEIN('11','12','13','14')ANDcard.flag='F';


2.优化方向

(1).主查询和子查询使用的表相同，条件差不多。考虑进行合并。

(2).

使用分析函数找出相同c_cust_id 既card.flag = 'F' 也 flag = '1' 或者只满足flag = '1' 然后将这部分记录过滤掉即可。

当分组结果card.flag = 'F' 也 flag = '1' min(flag) over(partition by card.c_cust_id) = '1'

当分组结果flag = '1' min(flag) over(partition by card.c_cust_id) = '1'

当分组结果flag = 'F' min(flag) over(partition by card.c_cust_id) = 'F' (需要)

selectcard.c_cust_id,card.TYPE,card.n_all_moneyfrom(selectcard.c_cust_id,card.TYPE,card.n_all_money,min(flag)over(partitionbycard.c_cust_id)fromcardwherecard.TYPEIN('11','12','13','14')andcard.flagin('1','F'))wherecard.flag='F';