select sum(case when goldmedal between 30 and 50 then 1 else 0 end) from ta
难道是这个意思???select goldmedal ,count(1) as cnt from ta where goldmedal between 30 and 50 group by goldmedal
就是计算字段GoldMedal为30的总数,GoldMedal为31的总数,直到50.
select goldmedal ,count(1) as cnt from ta where goldmedal between 30 and 50 group by goldmedal
select goldmedal ,count(1) as cnt, sum(...) as c from ta where goldmedal between 30 and 50 group by goldmedal
create table T(id int identity,GoldMedal int) insert into T select 20 insert into T select 30 insert into T select 31 insert into T select 32 insert into T select 41 insert into T select 50 insert into T select 53 insert into T select 93 declare @i int set @i = 30 declare @sql varchar(8000) set @sql='' while @i<=50 begin set @sql = @sql + '(select count(*) from T where GoldMedal='+cast(@i as varchar)+') as '''+cast(@i as varchar)+'小计'',' set @i = @i + 1 end exec('select '+@sql+'(select count(*) from T where GoldMedal between 30 and 50) as 合计')
create table ta(goldmedal int) insert into ta select 32 insert into ta select 50go select top 1000 px = identity(int,1,1) into tmp from sysobjects a,sysobjects b select b.px ,count(a.goldmedal) as cnt from ta a right join tmp b on a.goldmedal = b.px where b.px between 30 and 50 group by b.pxdrop table ta ,tmp /*px cnt ----------- ----------- 30 0 31 0 32 1 33 0 34 0 35 0 36 0 37 0 38 0 39 0 40 0 41 0 42 0 43 0 44 0 45 0 46 0 47 0 48 0 49 0 50 1(所影响的行数为 21 行) */
select [30的总数]=sum(case when goldmedal=30 then 1 else 0) , [31的总数]=sum(case when goldmedal=30 then 1 else 0), ...... from 表
--假设原表名为tblname,用存储过程实现(变量@s1,@s2是要查找的范围) create proc pr_rangeSum (@s1 int,@s2 int) as begin declare @i int if object_id('tembdb..#t') is not null drop table #t create table #t(id int identity) set identity_insert #t on set @i = @s1 while @i <= @s2 begin insert into #t(id) select @i set @i = @i + 1 endselect #t.id,isnull(count(t.goldmedal),0) from #t left join tblname t on #t.id = t.goldmedal group by #t.id end--调用 exec pr_rangeSum 30,50
select goldmedal ,count(*) as cnt from 表 where goldmedal = 30 union select goldmedal ,count(*) as cnt from 表 where goldmedal = 31 . . . . select goldmedal ,count(*) as cnt from 表 where goldmedal = 50
上面的有点小错误select goldmedal ,count(*) as cnt from 表 group by goldmedal having goldmedal =30 union select goldmedal ,count(*) as cnt from 表 group by goldmedal having goldmedal =31 . . . . select goldmedal ,count(*) as cnt from 表 group by goldmedal having goldmedal =50
select top 1000 px = identity(int,1,1) into tmp from sysobjects a,sysobjects b select b.px ,count(a.goldmedal) as cnt from ta a right join tmp b on a.goldmedal = b.px where b.px between 30 and 50 group by b.px
select sum(GoldMedal),(case when grouting(GoldMedal)==1 then '小计' else '' end) as temp1 from 表 where GoldMedal between 30 and 50 group by GoldMedal with cube
select goldmedal ,count(1) as cnt from ta where goldmedal between 30 and 50 group by goldmedal
from ta
难道是这个意思???select goldmedal ,count(1) as cnt
from ta
where goldmedal between 30 and 50
group by goldmedal
from ta
where goldmedal between 30 and 50
group by goldmedal
select goldmedal ,count(1) as cnt, sum(...) as c
from ta
where goldmedal between 30 and 50
group by goldmedal
insert into T select 20
insert into T select 30
insert into T select 31
insert into T select 32
insert into T select 41
insert into T select 50
insert into T select 53
insert into T select 93
declare @i int set @i = 30
declare @sql varchar(8000) set @sql=''
while @i<=50
begin
set @sql = @sql + '(select count(*) from T where GoldMedal='+cast(@i as varchar)+') as '''+cast(@i as varchar)+'小计'','
set @i = @i + 1
end
exec('select '+@sql+'(select count(*) from T where GoldMedal between 30 and 50) as 合计')
insert into ta select 32
insert into ta select 50go
select top 1000 px = identity(int,1,1) into tmp from sysobjects a,sysobjects b select b.px ,count(a.goldmedal) as cnt
from ta a right join tmp b
on a.goldmedal = b.px
where b.px between 30 and 50
group by b.pxdrop table ta ,tmp
/*px cnt
----------- -----------
30 0
31 0
32 1
33 0
34 0
35 0
36 0
37 0
38 0
39 0
40 0
41 0
42 0
43 0
44 0
45 0
46 0
47 0
48 0
49 0
50 1(所影响的行数为 21 行)
*/
[31的总数]=sum(case when goldmedal=30 then 1 else 0),
......
from 表
create proc pr_rangeSum
(@s1 int,@s2 int)
as
begin
declare @i int
if object_id('tembdb..#t') is not null
drop table #t
create table #t(id int identity) set identity_insert #t on set @i = @s1
while @i <= @s2
begin
insert into #t(id) select @i
set @i = @i + 1
endselect #t.id,isnull(count(t.goldmedal),0)
from #t left join tblname t
on #t.id = t.goldmedal
group by #t.id
end--调用
exec pr_rangeSum 30,50
select goldmedal ,count(*) as cnt
from 表
where goldmedal = 30
union
select goldmedal ,count(*) as cnt
from 表
where goldmedal = 31
.
.
.
.
select goldmedal ,count(*) as cnt
from 表
where goldmedal = 50
from 表
group by goldmedal having goldmedal =30
union
select goldmedal ,count(*) as cnt
from 表
group by goldmedal having goldmedal =31
.
.
.
.
select goldmedal ,count(*) as cnt
from 表
group by goldmedal having goldmedal =50
from ta a right join tmp b
on a.goldmedal = b.px
where b.px between 30 and 50
group by b.px
where GoldMedal between 30 and 50
group by GoldMedal with cube
from ta
where goldmedal between 30 and 50
group by goldmedal