这种情况有简便方法吗？

[firsk]

declare @table table(typeID int,id int)
insert into @table select 1,1
union all select 2,2
union all select 2,3
union all select 2,4
union all select 2,5
union all select 2,6
union all select 2,7
union all select 5,8
union all select 2,9


select (select count(*) from @table where typeID=1) one,
(select count(*) from @table where typeID=2) two,
(select count(*) from @table where typeID=3) three,
(select count(*) from @table where typeID=4) four,
(select count(*) from @table where typeID=5) five


--做以上统计，能不能不要每列都做一次子查询呢？
--望指教！！

[fcuandy]

select ne = count(case when typeid=1 then 4134 else null end),
tw = count(case when typeid=2 then 4134 else null end),
tr = count(case when typeid=3 then 1 else null end),
fo = count(case when typeid=4 then 35 else null end),
fv = count(case when typeid=5 then 15 else null end)
from @table


select ne = sum(case when typeid=1 then 1 else 0 end),
tw = sum(case when typeid=2 then 1 else 0 end),
tr = sum(case when typeid=3 then 1 else 0 end),
fo = sum(case when typeid=4 then 1 else 0 end),
fv = sum(case when typeid=5 then 1 else 0 end)
from @table

[xxsoft2007]

select sum(case when  typeID=1 then 1 else 0 end) as one
				,sum(case when  typeID=2 then 1 else 0 end) as two
				,sum(case when  typeID=3 then 1 else 0 end) as three
				,sum(case when  typeID=4 then 1 else 0 end) as four
				,sum(case when  typeID=5 then 1 else 0 end) as five
from @table 

[firsk]

谢谢二位

我还想问下问题，怎么做效率会更高呢？

[xxsoft2007]

那要看你 table 表中数据量的大小了，如果数据量大的话 后面一种方法效率要高些，否则你自己写的子查询方法就可以了

[xxsoft2007]

case when 效率比子查询的查询计划要优，也即效率要高点

[firsk]

多谢解答

[十一月猪]

select sum( case when typeid = 1 then 1 else 0 end ) one ,
sum( case when typeid = 2 then 1 else 0 end ) two ,
sum( case when typeid = 3 then 1 else 0 end ) three ,
sum( case when typeid = 4 then 1 else 0 end ) four ,
sum( case when typeid = 5 then 1 else 0 end ) five
from @table