sqlserver case when子查询使用in 查询时间过长

最新推荐文章于 2023-06-06 08:40:05 发布

豆苗子

最新推荐文章于 2023-06-06 08:40:05 发布

阅读量1.1k

点赞数

文章标签： sqlserver 数据库

本文链接：https://blog.csdn.net/weixin_44254608/article/details/128964810

版权

文章讨论了一种SQL查询优化的情况，原来在CASEWHEN语句中使用了IN操作符导致查询效率低下。作者根据业务逻辑改用了其他写法，从而提高了查询速度。查询涉及到的数据表包括ads_expi_pay_no_arrv_1w_dr和de_leader，以及对日期和部门数据的处理。

摘要由CSDN通过智能技术生成

在这里插入图片描述
本来是这么写的，在case when后面使用了in，查询非常缓慢
就根据业务逻辑，换了一种写法

源代码：

select t22.department,t22.leader,cast(t22.date2 as varchar(20)) as date2,t11.stat_date,t11.amt_sum,
left(cast(t22.date2 as varchar(20)),4) as year,
(CASE WHEN count(t11.amt_sum) over(partition by date2) = 0       
			     then 0
			     else 1 end
) as ifwrite -- 原本用的是case when in
from
(
 select t2.leader,t2.department,SUM(amt_sum) as amt_sum,t1.stat_date from 
 (
  select mng_2nd_name,stat_date,SUM(cast(amt as decimal(18,2))) as amt_sum 
  from ads_expi_pay_no_arrv_1w_dr where amt not like '%[^0-9.]%' and is_deleted='0' 
  group by mng_2nd_name,stat_date
 )t1 join de_leader t2 
 on t1.mng_2nd_name like '%'+t2.department+'%'
 group by t2.leader,t2.department,t1.stat_date
) t11
right join
(
select department,leader,date2,year from de_leader cross join
(
 select 
 DATEADD(week,number,'20200101') as day2,
 YEAR(DATEADD(day, 4 - DATEPART(iso_week, DATEADD(week,number,'20200101')), DATEADD(week,number,'20200101'))) * 100 + DATEPART(iso_week,DATEADD(week,number,'20200101')) as date2,
 YEAR(DATEADD(day, 4 - DATEPART(iso_week, DATEADD(week,number,'20200101')), DATEADD(week,number,'20200101'))) as year
 from master.dbo.spt_values sv
 where type = 'p' and (number BETWEEN 0 and DATEDIFF(week,'20200101',getdate()))
) t21
)t22
on t11.stat_date = t22.date2 and t11.leader=t22.leader and t11.department =t22.department

so，不要在case when后面用in~