select cola,colb,colc, sum( case when cold>1 and cold<10 then 1 else 0) as '1~10',sum( case when cold>11 and cold<20 then 1 else 0) as '11~20',sum( case when cold>21 and cold<30 then 1 else 0) as '21~30'from table
group by cola,colb,colc
group by cola,colb,colc
sum( case when cold>1 and cold<10 then 1 else 0 end ) as '1~10',
sum( case when cold>11 and cold<20 then 1 else 0 end) as '11~20',
sum( case when cold>21 and cold<30 then 1 else 0 end) as '21~30'from tablegroup by cola,colb,colc
go
insert into #table
select 'a','c','h',8
union
select 'a','c','h',9
union
select 'a','c','h',12
union
select 'a','c','h',22
union
select 'a','c','h',23
union
select 'a','c','h',23
goselect cola,colb,colc,
sum( case when cold>1 and cold<10 then 1 else 0 end ) as '1~10',
sum( case when cold>11 and cold<20 then 1 else 0 end) as '11~20',
sum( case when cold>21 and cold<30 then 1 else 0 end) as '21~30'from #table
group by cola,colb,colccola colb colc 1~10 11~20 21 ~30
-------------------------------------------------------
a c h 2 1 2
select @s='',@i=max(cold)/10
from 表
while @i>0
select @i1=cast(@i*10+1 as varchar)
,@i2=cast((@i+1)*10 as varchar)
,@s=',['+@i1+'-'+@i2+']=sum(case when cold between '
+@i1+' and '+@i2+' then 1 else 0 end)'+@s
,@i=@i-1
exec('select cola,colb,colc,[<=10]=sum(case when cold<=10 then 1 else 0 end)'+@s+'
from 表 group by cola,colb,colc')
create table 表 (cola varchar(1),colb varchar(1),colc varchar(1), cold int)
insert 表 select 'a','c','h',8
union all select 'a','c','h',9
union all select 'a','c','h',10
union all select 'a','c','h',11
union all select 'a','c','h',12
union all select 'a','c','h',22
union all select 'a','c','h',23
union all select 'a','c','h',23
go--统计
declare @s varchar(8000),@i int,@i1 varchar(10),@i2 varchar(10)
select @s='',@i=max(cold)/10
from 表
while @i>0
select @i1=cast(@i*10+1 as varchar)
,@i2=cast((@i+1)*10 as varchar)
,@s=',['+@i1+'-'+@i2+']=sum(case when cold between '
+@i1+' and '+@i2+' then 1 else 0 end)'+@s
,@i=@i-1
exec('select cola,colb,colc,[<=10]=sum(case when cold<=10 then 1 else 0 end)'+@s+'
from 表 group by cola,colb,colc')
go--删除测试结果
drop table 表/*--测试结果
cola colb colc <=10 11-20 21-30
---- ---- ---- ----------- ----------- -----------
a c h 3 2 3
--*/
我试了一下,好像不行(第2个)再说,查询时create table 是不是影响sql执行的性能能不能count()?