student表结构如下
name score
张飞 76
刘备 86
关羽 93
曹操 88
华佗 72
郭嘉 54
貂蝉 65
怎么按分数段进行分组查询我想要的结果是:
number temp
1 90-100
2 80-89
2 70-79
1 60-69
1 0-59
name score
张飞 76
刘备 86
关羽 93
曹操 88
华佗 72
郭嘉 54
貂蝉 65
怎么按分数段进行分组查询我想要的结果是:
number temp
1 90-100
2 80-89
2 70-79
1 60-69
1 0-59
temp='90-100'
from student union all
select number=sum(case when temp between 80 and 89 then 1 else 0 end),
temp='80-89'
from student
--下面的相同
insert into student select '张飞',76
insert into student select '刘备',86
insert into student select '关羽',93
insert into student select '曹操',88
insert into student select '华佗',72
insert into student select '郭嘉',54
insert into student select '貂蝉',65
go
select count(*)number,'90-100' temp from student where score between 90 and 100
union all
select count(*)number,'80-89' temp from student where score between 80 and 89
union all
select count(*)number,'70-79' temp from student where score between 70 and 79
union all
select count(*)number,'60-69' temp from student where score between 60 and 69
union all
select count(*)number,'0-59' temp from student where score<=60 order by 2 desc
/*
number temp
----------- ------
1 90-100
2 80-89
2 70-79
1 60-69
1 0-59(5 行受影响)*/
go
drop table student
sum(case when score between 90 and 100 then 1 else 0 end) as '90-100',
sum(case when score between 80 and 89 then 1 else 0 end) as '80-89',
...
from
tb
case
when score between 90 and 100 then '90-100',
when score between 80 and 89 then '80-89',
when score between 70 and 79 then '70-79',
when score between 60 and 69 then '60-69',
when score between 0 and 59 then '0-59'
end temp
from student
group by
case
when score between 90 and 100 then '90-100',
when score between 80 and 89 then '80-89',
when score between 70 and 79 then '70-79',
when score between 60 and 69 then '60-69',
when score between 0 and 59 then '0-59'
end
insert into student values('张飞',76)
insert into student values('刘备',86)
insert into student values('关羽',93)
insert into student values('曹操',88)
insert into student values('华佗',72)
insert into student values('郭嘉',54)
insert into student values('貂蝉',65)
goselect count(1) number,
case
when score between 90 and 100 then '90-100'
when score between 80 and 89 then '80-89'
when score between 70 and 79 then '70-79'
when score between 60 and 69 then '60-69'
when score between 0 and 59 then '0-59'
end temp
from student
group by
case
when score between 90 and 100 then '90-100'
when score between 80 and 89 then '80-89'
when score between 70 and 79 then '70-79'
when score between 60 and 69 then '60-69'
when score between 0 and 59 then '0-59'
end
order by temp descdrop table student/*
number temp
----------- ------
1 90-100
2 80-89
2 70-79
1 60-69
1 0-59(所影响的行数为 5 行)
*/
(
[NAME] VARCHAR(20)
, [SCORE] INT
)INSERT INTO STUDENT
SELECT '张飞', 76
UNION ALL
SELECT '刘备', 86
UNION ALL
SELECT '关羽', 93
UNION ALL
SELECT '曹操', 88
UNION ALL
SELECT '华佗', 72
UNION ALL
SELECT '郭嘉', 54
UNION ALL
SELECT '貂蝉', 65SELECT COUNT(1) AS [NUMBER]
, [TEMP]
FROM
(
SELECT CASE
WHEN SCORE < 60 THEN '0-59'
WHEN SCORE < 70 THEN '60-69'
WHEN SCORE < 80 THEN '70-79'
WHEN SCORE < 90 THEN '80-89'
ELSE '90-100'
END AS [TEMP]
FROM STUDENT
) T
GROUP BY [TEMP]
ORDER BY [TEMP] DESCDROP TABLE STUDENT
--> 测试数据: [student]
if object_id('[student]') is not null drop table [student]
create table [student] (name varchar(4),score int)
insert into [student]
select '张飞',76 union all
select '刘备',86 union all
select '关羽',93 union all
select '曹操',88 union all
select '华佗',72 union all
select '郭嘉',54 union all
select '貂蝉',65--开始查询
;with cte as( --列
select
sum(case when score between 90 and 100 then 1 else 0 end) as '90-100',
sum(case when score between 80 and 89 then 1 else 0 end) as '80-89',
sum(case when score between 70 and 79 then 1 else 0 end) as '70-79',
sum(case when score between 60 and 69 then 1 else 0 end) as '60-69',
sum(case when score between 0 and 59 then 1 else 0 end) as '0-59'
from student
)
select score as number,student as temp from cte --列转行
unpivot
(score for student in([90-100],[80-89],[70-79],[60-69],[0-59]))b--结束查询
drop table [student]/*
number temp
----------- -------------------------------
1 90-100
2 80-89
2 70-79
1 60-69
1 0-59(5 行受影响)