现在有一张表.201401, 如下:
ID ka_pm kb_pm kc_pm kd_pm ke_pm kf_pm
其中,ID的字段是不会重复的. 其他字段有重复值, 比如:'BS-ID80'在除ID个的列中都会出现(每行只会出现一次.,但不是每个ID都会有)现在是要统计'BS-ID80'出现了多少次.
我自己写的语句如下:
declare @callname varchar(10)
set @callname='BS-ID80'
select COuNT(*) from share20140410.dbo.[201401] where ka_pm=@callname union all
select COuNT(*) from share20140410.dbo.[201401] where kb_pmc=@callname union all
select COuNT(*) from share20140410.dbo.[201401] where kc_pm=@callname union all
select COuNT(*) from share20140410.dbo.[201401] where kd_pm=@callname union all
select COuNT(*) from share20140410.dbo.[201401] where ke_pm=@callname union all
select COuNT(*) from share20140410.dbo.[201401] where kf_pm=@callname
得到的结果如下
1 0
2 0
3 0
4 18
5 8
我的问题是:
1 怎样才能直接得到结果是26
2. 以上的语句能否简化
3 如果用遍历的思路,如何来写?
谢谢各位大拿
------解决方案--------------------
SELECT COUNT(*)
FROM share20140410.dbo.[201401]
WHERE ka_pm = @callname
OR kb_pmc = @callname
OR kc_pm = @callname
OR kd_pm = @callname
OR ke_pm = @callname
OR kf_pm = @callname
------解决方案--------------------
select
sum(case when ka_pm=@callname then 1 else 0 end) as num
from
(select ka_pm from share20140410.dbo.[201401]
union all
select kb_pmc from share20140410.dbo.[201401]
...
) as t
------解决方案--------------------
1.select COuNT(*) from share20140410.dbo.[201401] where ka_pm=@callname or kb_pmc=@callname or.....
改成or查询，因为每行只会出现一次'BS-ID80'
2.同上
3.遍历可以用来求总数，但并不适用于你这样的情况，你有兴趣可以去网上查with递归