【SQL改写】notexists-leftjoin(distinct)whereisnull改写_标量子查询
来源:互联网 发布:jquery调用js函数 编辑:程序博客网 时间:2024/06/05 14:19
- --原SQL 30min出不来结果
- select a.*
- from TeableA a
- where is_open = '1'
- and ecd = 'xshg'
- and not exists
- (select * from bvaluation b
- where a.calendar_date = b.tdate)
- and a.calendar_date >= '2011-09-06'
- and a.calendar_date < convert( date, getdate())
- --表信息查看
- select count(1)
- from TeableA a
- where is_open = '1'
- and ecd = 'xshg'
- and a.calendar_date >= '2011-09-06'
- and a.calendar_date < convert( date, getdate())
- --1357
- select count(1) from bvaluation b
- --8262721
- --改写SQL1
- select
- a.*
- from TeableA a left join tdate b on (a.calendar_date = b.tdate)
- where
- a.is_open = '1' and
- a.ecd = 'xshg' and
- a.calendar_date >= '2011-09-06' and
- a.calendar_date < convert( date,getdate()) and
- b.tdate is null
- --发现a.calendar_date: b.tdate=1:N ,join后会导致a表的重复,查询反馈很快,但是因为重复result set跑完耗时还是较长。
- --sp_helpindex bvaluation
- --bvaluation$BPK_AK_Key nonclustered, unique, unique key located on DATA SID, TSYMBOL, tdate, YEART_MAT
- --唯一约束中包含tdate
- --改写SQL2(Final)
- select
- a.*
- from TeableA a left join (select distinct tdate from bvaluation)
- b on (a.calendar_date = b.tdate)
- where
- a.is_open = '1' and
- a.ecd = 'xshg' and
- a.calendar_date >= '2011-09-06' and
- a.calendar_date < convert( date,getdate()) and
- b.tdate is null
- --1s之内出结果
阅读全文
0 0
- 【SQL改写】notexists-leftjoin(distinct)whereisnull改写_标量子查询
- 【SQL改写】notexists-leftjoin(distinct)whereisnull改写
- 标量子查询SQL改写
- 标量子查询改写
- SQL优化-标量子查询的改写
- SQL改写, 聚合语句, left join 改写标量子查询
- 标量子查询SQL改写一则(包括WITH的改写)
- 不等值标量子查询改写
- left outer join 改写标量子查询
- oracle标量子查询简介和表连接改写
- SQL中带有NOT IN 子查询改写
- sql 标量子查询
- 优化系列 | DELETE子查询改写优化
- 优化系列 | DELETE子查询改写优化
- 改写SQL优化SQL
- 标量子查询优化(用group by 代替distinct)
- Oracle 查询优化改写
- 子查询解嵌套not in 无法展开改写
- 二叉树
- python first lesson
- 对pytroch中torch.autograd.backward的思考
- Java的流程控制
- struts2_day01_10_struts2常量配置
- 【SQL改写】notexists-leftjoin(distinct)whereisnull改写_标量子查询
- Spring Boot热部署的实现
- 数据结构的链栈
- android 旋转动画
- java基础经典总结
- 读书笔记——深入理解JAVA虚拟机(2)
- Android倒计时 CountDownTimer的使用
- xcode9元素约束设置
- Linux学习