代码如下
if @art2='7A'
begin
select bar9 as model,color as col,
sum(case when authority = 'TT' then quantity else 0 end ) as et_tt,
sum(case when authority = 'OH' and a.d_t > b.d_t_Last then quantity else 0 end ) as et_oh,
sum(case when authority = 'HO' and a.d_t > b.d_t_Last then quantity else 0 end) as et_ho,
sum(case when authority = 'SS' and a.d_t > b.d_t_Last then quantity else 0 end ) as et_ss
into #temp5s
from etamsellt a
inner join #TempTT b on a.shopno =b.shop_no and (a.d_t between b.d_t_Last and @date2)
where (bar9 like @art2 + '%') or bar9='7B8I02038' or bar9='7B8C18025' or bar9='7B7D18020' or bar9='7B8B18027' or bar9='7B0C08056' or bar9='7B0A08008' or bar9='7B0C08051'
group by a.bar9,a.color
end
else
begin
select bar9 as model,color as col,
sum(case when authority = 'TT' then quantity else 0 end ) as et_tt,
sum(case when authority = 'OH' and a.d_t > b.d_t_Last then quantity else 0 end ) as et_oh,
sum(case when authority = 'HO' and a.d_t > b.d_t_Last then quantity else 0 end) as et_ho,
sum(case when authority = 'SS' and a.d_t > b.d_t_Last then quantity else 0 end ) as et_ss
into #temp5s
from etamsellt a
inner join #TempTT b on a.shopno =b.shop_no and (a.d_t between b.d_t_Last and @date2)
where (bar9 like @art2 + '%')
group by a.bar9,a.color
end
这是个分支语句,但是在检查语法的时候报#temp5s已经存在,但是如果按照这个语句的意思应该只会创建一个@temp5s,应该不存在重复的可能。如果只留一个分支语句就通过了,不知哪位可以指点一下是怎么回事
if @art2='7A'
begin
select bar9 as model,color as col,
sum(case when authority = 'TT' then quantity else 0 end ) as et_tt,
sum(case when authority = 'OH' and a.d_t > b.d_t_Last then quantity else 0 end ) as et_oh,
sum(case when authority = 'HO' and a.d_t > b.d_t_Last then quantity else 0 end) as et_ho,
sum(case when authority = 'SS' and a.d_t > b.d_t_Last then quantity else 0 end ) as et_ss
into #temp5s
from etamsellt a
inner join #TempTT b on a.shopno =b.shop_no and (a.d_t between b.d_t_Last and @date2)
where (bar9 like @art2 + '%') or bar9='7B8I02038' or bar9='7B8C18025' or bar9='7B7D18020' or bar9='7B8B18027' or bar9='7B0C08056' or bar9='7B0A08008' or bar9='7B0C08051'
group by a.bar9,a.color
end
else
begin
select bar9 as model,color as col,
sum(case when authority = 'TT' then quantity else 0 end ) as et_tt,
sum(case when authority = 'OH' and a.d_t > b.d_t_Last then quantity else 0 end ) as et_oh,
sum(case when authority = 'HO' and a.d_t > b.d_t_Last then quantity else 0 end) as et_ho,
sum(case when authority = 'SS' and a.d_t > b.d_t_Last then quantity else 0 end ) as et_ss
into #temp5s
from etamsellt a
inner join #TempTT b on a.shopno =b.shop_no and (a.d_t between b.d_t_Last and @date2)
where (bar9 like @art2 + '%')
group by a.bar9,a.color
end
这是个分支语句,但是在检查语法的时候报#temp5s已经存在,但是如果按照这个语句的意思应该只会创建一个@temp5s,应该不存在重复的可能。如果只留一个分支语句就通过了,不知哪位可以指点一下是怎么回事
drop table #temp5s
再保存该存储过程
第二次用insert into ..select...