sql1:select khxm,dl,xm,(select COUNT(*) from jcxx b where a.xm=b.inspect_name and CHARINDEX('*'+a.khxm+'*','*'+b.khdl)>0) as 完成次数 into #w from dlxmb a
sql2:select xm,(case when exists (select 1 from #w where xm = t.xm and isnull([完成次数],0) < isnull(dl,0)) then N'未完成' else N'完成' end) [完成情况]
from #w t
group by xm
各位老师谁能帮我把这两条SQL合成一句啊,我现在是sql1查询建临时表供sql2查询
sql2:select xm,(case when exists (select 1 from #w where xm = t.xm and isnull([完成次数],0) < isnull(dl,0)) then N'未完成' else N'完成' end) [完成情况]
from #w t
group by xm
各位老师谁能帮我把这两条SQL合成一句啊,我现在是sql1查询建临时表供sql2查询
select xm,(case when exists (select 1 from #w where xm = t.xm and isnull([完成次数],0) < isnull(dl,0)) then N'未完成' else N'完成' end) [完成情况]
from
(
select khxm,dl,xm,(select COUNT(*) from jcxx b where a.xm=b.inspect_name and CHARINDEX('*'+a.khxm+'*','*'+b.khdl)>0) as 完成次数 from dlxmb a
)
t
group by xm
--哦,2楼改漏了个地方。不过这样改效率并不高,最好说出你的需求,让大家帮你换种思路写语句。
select xm,(case when exists (select 1 from (select khxm,dl,xm,(select COUNT(*) from jcxx b where a.xm=b.inspect_name and CHARINDEX('*'+a.khxm+'*','*'+b.khdl)>0) as 完成次数 from dlxmb a)t where xm = t.xm and isnull([完成次数],0) < isnull(dl,0)) then N'未完成' else N'完成' end) [完成情况]
from
(
select khxm,dl,xm,(select COUNT(*) from jcxx b where a.xm=b.inspect_name and CHARINDEX('*'+a.khxm+'*','*'+b.khdl)>0) as 完成次数 from dlxmb a
)
t
group by xm
--try:select xm,完成情况=case when sum(完成情况)>0 then '完成' else '未完成' end from
(select xm,完成情况=case when exists(select 1 from jcxx b where a.xm=b.inspect_name and CHARINDEX('*'+a.khxm+'*','*'+b.khdl)>0) then 1 else -1 end from dlxmb a )t
group by xm