分析函数改写not in

1.OLD:

SELECT card.c_cust_id, card.TYPE, card.n_all_money  FROM card WHERE     card.c_cust_id NOT IN (SELECT c_cust_id                                    FROM card                                   WHERE     TYPE IN ('11',                                                      '12',                                                      '13',                                                      '14')                                         AND flag = '1')       AND card.TYPE IN ('11',                         '12',                         '13',                         '14')       AND card.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' (需要)

select card.c_cust_id, card.TYPE, card.n_all_moneyfrom (select card.c_cust_id,             card.TYPE,                         card.n_all_money,                         min(flag) over(partition by card.c_cust_id)          from card          where card.TYPE IN ('11',                         '12',                         '13',                         '14')                 and card.flag in ('1','F'))where card.flag = 'F';