分析函数改写notin
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_money from (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';
文章标题:分析函数改写notin
标题路径:http://pwwzsj.com/article/jcgjis.html