求助：用一条sql语句实现这样的分组查询
表如下
name grade
－－－－－－－－－－
a1 45
a2 50
a3 54
a4 65
a5 66
a6 77
a7 89
a8 80
a9 91
a10 90
a11 100
用一条sql语句查询grade在60以下,60~69、70~79、80~89、90~100的人数各有多少！
要显示如下效果：
scale count
——————————
60以下 3
60~69 2
70~79 1
80~89 2
90~100 3
和以下效果：
60以下 60~69 70~79 80~89 90~100
——————————————————————————
3 2 1 2 3
[解决办法]
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 100
select '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(20),grade int)insert @t select 'a1',45insert @t select 'a2',50insert @t select 'a3',54insert @t select 'a4',65insert @t select 'a5',66insert @t select 'a6',77insert @t select 'a7',89insert @t select 'a8',80insert @t select 'a9',91insert @t select 'a10',90insert @t select 'a11',100select '60以下'=(select count(name) from (select * from @t where grade <60) t ),'60~69'=(select count(name) from (select * from @t where grade <69 and grade>60) t ),'70~79'=(select count(name) from (select * from @t where grade <79 and grade>70) t ),'80~89'=(select count(name) from (select * from @t where grade <89 and grade>80) t ),'90~100'=(select count(name) from (select * from @t where grade <100 and grade>90) t )/*(1 row(s) affected)60以下 60~69 70~79 80~89 90~100----------- ----------- ----------- ----------- -----------3 2 1 0 1(1 row(s) affected)*/
