用一条sql语句实现这样的分组查询
求助:用一条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
自己该字段和表名
------解决方案--------------------
表如下
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
自己该字段和表名
------解决方案--------------------
- SQL code
declare @t table(name varchar(20),grade int) insert @t select 'a1',45 insert @t select 'a2',50 insert @t select 'a3',54 insert @t select 'a4',65 insert @t select 'a5',66 insert @t select 'a6',77 insert @t select 'a7',89 insert @t select 'a8',80 insert @t select 'a9',91 insert @t select 'a10',90 insert @t select 'a11',100 select '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) */