select '60以下',count(*) from ha where grade <60
union all
select '60-69',count(*) from ha where grade between 60 and 69
union all
select '70-79',count(*) from ha where grade between 70 and 79
union all
select '80-89',count(*) from ha where grade between 80 and 89
union all
select '90-100',count(*) from ha where grade between 90 and 100select '60以下'=(select count(*) from ha where grade <60),
'60-69'=(select count(*) from ha where grade between 60 and 69),
'70-79'=(select count(*) from ha where grade between 70 and 79),
'80-89'=(select count(*) from ha where grade between 80 and 89),
'90-100'=count(*) from ha where grade between 90 and 100
union all
select '60-69',count(*) from ha where grade between 60 and 69
union all
select '70-79',count(*) from ha where grade between 70 and 79
union all
select '80-89',count(*) from ha where grade between 80 and 89
union all
select '90-100',count(*) from ha where grade between 90 and 100select '60以下'=(select count(*) from ha where grade <60),
'60-69'=(select count(*) from ha where grade between 60 and 69),
'70-79'=(select count(*) from ha where grade between 70 and 79),
'80-89'=(select count(*) from ha where grade between 80 and 89),
'90-100'=count(*) from ha where grade between 90 and 100
declare @T table(name varchar(3),grade int)
insert @T
select 'a1',45 union all
select 'a2',50 union all
select 'a3',54 union all
select 'a4',65 union all
select 'a5',66 union all
select 'a6',77 union all
select 'a7',89 union all
select 'a8',80 union all
select 'a9',91 union all
select 'a10',90 union all
select 'a11',100select scale=case when grade/10<6 then '60以下' when grade/10 in(9,10) then '90~100' else ltrim(grade/10)+'0~'+ltrim(grade/10)+'9' end,count=count(*) from @T group by case when grade/10<6 then '60以下' when grade/10 in(9,10) then '90~100' else ltrim(grade/10)+'0~'+ltrim(grade/10)+'9' endselect [60以下]=sum(case when grade/10<6 then 1 end),[60~69]=sum(case grade/10 when 6 then 1 end),[70~79]=sum(case grade/10 when 7 then 1 end),[80~89]=sum(case grade/10 when 8 then 1 end),[90~100]=sum(case when grade/10 in(9,10) then 1 end) from @T/*scale count
--------------------------- -----------
60~69 2
60以下 3
70~79 1
80~89 2
90~100 3(所影响的行数为 5 行)60以下 60~69 70~79 80~89 90~100
----------- ----------- ----------- ----------- -----------
3 2 1 2 3(所影响的行数为 1 行)*/
insert @T select 'a1', 45
union all select 'a2', 50
union all select 'a3', 54
union all select 'a4', 65
union all select 'a5', 66
union all select 'a6', 77
union all select 'a7', 89
union all select 'a8', 80
union all select 'a9', 91
union all select 'a10', 90
union all select 'a11', 100--增加一列排序[Order]
select
[scale]=case when grade<60 then '60以下'
when grade between 60 and 69 then '60~69'
when grade between 70 and 79 then '70~79'
when grade between 80 and 89 then '80~89'
when grade between 90 and 100 then '90~100'
else '100以上' end,
[count]=count(1),
[Order]=case when grade<60 then 1
when grade between 60 and 69 then 2
when grade between 70 and 79 then 3
when grade between 80 and 89 then 4
when grade between 90 and 100 then 5
else 6 end
from
@t
group by case when grade<60 then '60以下'
when grade between 60 and 69 then '60~69'
when grade between 70 and 79 then '70~79'
when grade between 80 and 89 then '80~89'
when grade between 90 and 100 then '90~100'
else '100以上' end,
case when grade<60 then 1
when grade between 60 and 69 then 2
when grade between 70 and 79 then 3
when grade between 80 and 89 then 4
when grade between 90 and 100 then 5
else 6 end
order by
[Order]
--------------
select
[60以下]=sum(case when grade<60 then 1 else 0 end),
[60~69]=sum(case when grade between 60 and 69 then 1 else 0 end),
[70~79]=sum(case when grade between 70 and 79 then 1 else 0 end),
[80~89]=sum(case when grade between 80 and 89 then 1 else 0 end),
[90~100]=sum(case when grade between 80 and 89 then 1 else 0 end)from
@t
---(所影响的行数为 11 行)scale count Order
------- ----------- -----------
60以下 3 1
60~69 2 2
70~79 1 3
80~89 2 4
90~100 3 5(所影响的行数为 5 行)60以下 60~69 70~79 80~89 90~100
----------- ----------- ----------- ----------- -----------
3 2 1 2 2(所影响的行数为 1 行)