优化二

us_yunleiwang 2011-04-14 02:14:45
看看这个语句的优化? 能不能用 NOT EXISTS 替代 not in

LifeMir.dbo.CMSUSREL A (CRCO— 主健)
LifeMir.dbo.NBSPCNTR (PCCO — 主健) 两张表关联

PCUSR6 like 'C__9%' and

(PCAPPY*10000+PCAPPM*100+PCAPPD)=CONVERT(varchar(12) , getdate(), 112));
可不可以用试图替换:

大家帮优化一下
--------------------------------------------------------------------------
select
Distinct CRCTL1 as PolicyID
from LifeMir.dbo.CMSUSREL A
left join LifeMir.dbo.NBSPCNTR B
On A.CRCTL1=B.PCPOLN
where (CRALPH ='OW1' or (CRALPH='INS' and CRCTL2=0))

AND CRCTL1 NOT IN ( select PCPOLN from LifeMir.dbo.NBSPCNTR where PCUSR6 like 'C__9%' and

(PCAPPY*10000+PCAPPM*100+PCAPPD)=CONVERT(varchar(12) , getdate(), 112));
...全文
86 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
us_yunleiwang 2011-04-14
  • 打赏
  • 举报
回复
select
Distinct CRCTL1 as PolicyID
from LifeMir.dbo.CMSUSREL A
left join LifeMir.dbo.NBSPCNTR B
On A.CRCTL1=B.PCPOLN
where (CRALPH ='OW1' or (CRALPH='INS' and CRCTL2=0))

AND CRCTL1 NOT IN ( select PCPOLN from LifeMir.dbo.NBSPCNTR where PCUSR6 like 'C__9%' and

(PCAPPY*10000+PCAPPM*100+PCAPPD)=CONVERT(varchar(12) , getdate(), 112));
(59777 行受影响)
而你的
select
Distinct CRCTL1 as PolicyID
from LifeMir.dbo.CMSUSREL A
left join LifeMir.dbo.NBSPCNTR B
On A.CRCTL1=B.PCPOLN
where
(CRALPH ='OW1' or (CRALPH='INS' and CRCTL2=0))

AND
NOT exists( select 1 from LifeMir.dbo.NBSPCNTR where PCUSR6 like 'C__9%' and

(PCAPPY*10000+PCAPPM*100+PCAPPD)=CONVERT(varchar(12) , getdate(), 112 and CRCTL1=a.CRCTL1));
影响行数
(0 行受影响)
--小F-- 2011-04-14
  • 打赏
  • 举报
回复
EXISTS和IN效率差不多的
--小F-- 2011-04-14
  • 打赏
  • 举报
回复
select  
Distinct CRCTL1 as PolicyID
from LifeMir.dbo.CMSUSREL A
left join LifeMir.dbo.NBSPCNTR B
On A.CRCTL1=B.PCPOLN
where
(CRALPH ='OW1' or (CRALPH='INS' and CRCTL2=0))

AND
NOT Iexists( select 1 from LifeMir.dbo.NBSPCNTR where PCUSR6 like 'C__9%' and

(PCAPPY*10000+PCAPPM*100+PCAPPD)=CONVERT(varchar(12) , getdate(), 112 and CRCTL1=a.CRCTL1));
快溜 2011-04-14
  • 打赏
  • 举报
回复

--这样不行?
select
Distinct CRCTL1 as PolicyID
from LifeMir.dbo.CMSUSREL A
left join LifeMir.dbo.NBSPCNTR B
On A.CRCTL1=B.PCPOLN
where (CRALPH ='OW1' or (CRALPH='INS' and CRCTL2=0))
and PCUSR6 like 'C__9%' and
(PCAPPY*10000+PCAPPM*100+PCAPPD)=CONVERT(varchar(12) , getdate(), 112)
ado_cc 2011-04-14
  • 打赏
  • 举报
回复
快溜 2011-04-14
  • 打赏
  • 举报
回复
用not exists跟not in差不多。不算优化

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧