3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
这种情况有简便方法吗？
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
--做以上统计，能不能不要每列都做一次子查询呢？
--望指教！！
...全文
请发表友善的回复…
发表回复
十一月猪 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