本来是这么写的,在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~