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
--做以上统计,能不能不要每列都做一次子查询呢?
--望指教!!
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
--做以上统计,能不能不要每列都做一次子查询呢?
--望指教!!
解决方案 »
- 帮忙解决一个问题,分不多了,全给了,谢谢大家。
- 发生错误 1069-(由于登录失败而无法启动服务。)
- 还是关于自动调度的问题
- Microsoft Visual Studio 2008 Team Foundation Server 安装问题!急!高分相赠。
- 关于离线数据库更新
- 连续时间内某字段的最大值计算,答对即给分.
- 怪了,有问题!!帮我下。
- 关于SQLSERVER中如何得到形如 4-15-2009 格式的日期
- a,b,c,d四個球!!!
- 问个sql的紧急问题,TO_DAYS,TIME_TO_SEC
- 一个自定义SQL函数的苦恼,请高手帮忙,看这问题是什么原因?
- ACEESS中的备注类型在sql中应该是什么??
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
,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
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