• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

这种情况有简便方法吗?

firsk 2007-12-11 05:49:06
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


--做以上统计,能不能不要每列都做一次子查询呢?
--望指教!!
...全文
88 点赞 收藏 7
写回复
7 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
十一月猪 2007-12-12

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
回复
firsk 2007-12-12
多谢解答
回复
xxsoft2007 2007-12-11
case when 效率比子查询的查询计划要优,也即效率要高点
回复
xxsoft2007 2007-12-11
那要看你 table 表中数据量的大小了,如果数据量大的话 后面一种方法效率要高些,否则你自己写的子查询方法就可以了
回复
firsk 2007-12-11
谢谢二位

我还想问下问题,怎么做效率会更高呢?
回复
xxsoft2007 2007-12-11
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
回复
fcuandy 2007-12-11
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
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2007-12-11 05:49
社区公告
暂无公告