Select * from (
Select f2,sum(case when f1 between 1 and 3 then 1 else 0 end) as count,'1-3' as comp
from 表 group by f2,'1-3'
Union all
Select f2,sum(case when f1 between 1 and 3 then 1 else 0 end) as count,'4-8' as comp
from 表 group by f2,'4-8'
Union all
Select f2,sum(case when f1 between 1 and 3 then 1 else 0 end) as count,'>8' as comp
from 表 group by f2,'>8'
) bb
order by f2,comp
Select f2,sum(case when f1 between 1 and 3 then 1 else 0 end) as count,'1-3' as comp
from 表 group by f2,'1-3'
Union all
Select f2,sum(case when f1 between 1 and 3 then 1 else 0 end) as count,'4-8' as comp
from 表 group by f2,'4-8'
Union all
Select f2,sum(case when f1 between 1 and 3 then 1 else 0 end) as count,'>8' as comp
from 表 group by f2,'>8'
) bb
order by f2,comp
Select * from (
Select f2,sum(case when f1 between 1 and 3 then 1 else 0 end) as count,'1-3' as comp
from 表 group by f2
Union all
Select f2,sum(case when f1 between 1 and 3 then 1 else 0 end) as count,'4-8' as comp
from 表 group by f2
Union all
Select f2,sum(case when f1 between 1 and 3 then 1 else 0 end) as count,'>8' as comp
from 表 group by f2
) bb
order by f2,comp
Select * from (
Select f2,sum(case when f1 between 1 and 3 then 1 else 0 end) as count,'1-3' as comp
from 表 group by f2
Union all
Select f2,sum(case when f1 between 4 and 8 then 1 else 0 end) as count,'4-8' as comp
from 表 group by f2
Union all
Select f2,sum(case when f1 >8 then 1 else 0 end) as count,'>8' as comp
from 表 group by f2
) bb
order by f2,comp
或
Select * from (
Select f2,sum(1) as count,'1-3' as comp from 表 where f1 between 1 and 3 group by f2
Union all
Select f2,sum(1) as count,'4-8' as comp from 表 where f1 between 4 and 8 group by f2
Union all
Select f2,sum(1) as count,'>' as comp from 表 where f1 >8 group by f2
) bb
order by f2,comp
insert @table1 values( 1 , '区域1')
insert @table1 values( 3 , '区域1')
insert @table1 values( 5 , '区域1')
insert @table1 values( 6 ,'区域1')
insert @table1 values( 8 , '区域1')
insert @table1 values( 1 , '区域2')
insert @table1 values( 4 , '区域2')
insert @table1 values( 6 , '区域2')
insert @table1 values( 3 , '区域3')
insert @table1 values( 5 , '区域3')
insert @table1 values( 9 , '区域3')
insert @table1 values( 4, '区域4')select b.f2,isnull(c.[count],0) [count],a.comp from (select '1-3' comp union all select '4-8' union all select '>8') a join (select distinct f2 from @table1) b on 1=1 left join (select f2,count(*) [count],case when f1 between 1 and 3 then '1-3' when f1 between 4 and 8 then '4-8' else '>8' end comp from @table1 group by all f2,case when f1 between 1 and 3 then '1-3' when f1 between 4 and 8 then '4-8' else '>8' end) c on a.comp=c.comp and b.f2=c.f2
---------- ----------- ----
区域1 2 1-3
区域1 3 4-8
区域1 0 >8
区域2 1 1-3
区域2 2 4-8
区域2 0 >8
区域3 1 1-3
区域3 1 4-8
区域3 1 >8
区域4 0 1-3
区域4 1 4-8
区域4 0 >8
insert @table1 values( 1 , '区域1')
insert @table1 values( 3 , '区域1')
insert @table1 values( 5 , '区域1')
insert @table1 values( 6 ,'区域1')
insert @table1 values( 8 , '区域1')
insert @table1 values( 1 , '区域2')
insert @table1 values( 4 , '区域2')
insert @table1 values( 6 , '区域2')
insert @table1 values( 3 , '区域3')
insert @table1 values( 5 , '区域3')
insert @table1 values( 9 , '区域3')
insert @table1 values( 4, '区域4')select b.f2,isnull(c.[count],0) [count],a.comp from (select '1-3' comp union all select '4-8' union all select '>8') a join (select distinct f2 from @table1) b on 1=1 left join (select f2,count(*) [count],case when f1 between 1 and 3 then '1-3' when f1 between 4 and 8 then '4-8' else '>8' end comp from @table1 group by all f2,case when f1 between 1 and 3 then '1-3' when f1 between 4 and 8 then '4-8' else '>8' end) c on a.comp=c.comp and b.f2=c.f2 order by b.f2,charindex(a.comp,'1-3,4-8,>8')
-------------结果：f2 count comp
---------- ----------- ----
区域1 2 1-3
区域1 3 4-8
区域1 0 >8
区域2 1 1-3
区域2 2 4-8
区域2 0 >8
区域3 1 1-3
区域3 1 4-8
区域3 1 >8
区域4 0 1-3
区域4 1 4-8
区域4 0 >8（所影响的行数为 12 行）
case when f1 between 1 and 3 then '1-3' when f1 between 4 and 8 then '4-8' else '>8' end comp
from @table1 group by f2,
case when f1 between 1 and 3 then '1-3' when f1 between 4 and 8 then '4-8' else '>8' end
order by f2----------结果：f2 count comp
---------- ----------- ----
区域1 2 1-3
区域1 3 4-8
区域2 1 1-3
区域2 2 4-8
区域3 1 >8
区域3 1 1-3
区域3 1 4-8
区域4 1 4-8（所影响的行数为 8 行）
insert @t values( 1 , '區域1')
insert @t values( 3 , '區域1')
insert @t values( 5 , '區域1')
insert @t values( 6 ,'區域1')
insert @t values( 8 , '區域1')
insert @t values( 1, '區域2')
insert @t values( 4 ,'區域2')
insert @t values( 6 ,'區域2')
insert @t values( 3, '區域3')
insert @t values( 5, '區域3')
insert @t values( 9, '區域3')
insert @t values( 4,'區域4')
select * from (
select g.n2 ,isnull(c.count,0) as count,isnull(c.cmp,'>8') as cmp from (select distinct n2 from @t ) g
left outer join
(select n2, count(n1) as count,'>8' as cmp from @t where n1>8 group by n2 ) c
on g.n2=c.n2
union
select d.n2 ,isnull(a.count,0) as count,isnull(a.cmp,'1-3') as cmp from (select distinct n2 from @t ) d left outer join
(select n2, count(n1) as count,'1-3' as cmp from @t where n1 between 1 and 3 group by n2)a
on d.n2=a.n2 union
select f.n2 ,isnull(b.count,0) as count,isnull(b.cmp,'4-8') as cmp from (select distinct n2 from @t ) f
left outer join
(select n2, count(n1) as count,'4-8' as cmp from @t where n1 between 4 and 8 group by n2)b
on f.n2=b.n2 ) j
order by j.n2,len(j.cmp) desc
case when f1 between 1 and 3 then '1-3' when f1 between 4 and 8 then '4-8' else '>8' end