SQL查询笔记（行转列）

有两张表

1.查询所有性别的同学成绩都为优
select
name,
subject,
scope,
case sex when 1 then ‘男’ when 2 then ‘女’ else ‘其它’ end sex,
‘优’ level
from test
left join student on test.student_id=student.id

2.查询各成绩段显示的级别
select
name,subject,scope,case when scope>=90 then ‘优’
when scope>=70 then ‘良’
when scope>=60 then ‘及格’
else’不及格’
end level
from test
left join student on test.student_id=student.id

3.三科总分排名
第一种方法：
select Student.name,Student.id,Student.sex,
SUM(case test.subject when ‘语文’ then scope else 0 end)as 语文,
SUM(case test.subject when ‘数学’ then scope else 0 end)as 数学,
SUM(case test.subject when ‘英语’ then scope else 0 end)as 英语,
SUM(scope)as 总分
from Student
left join test on test.student_id=Student.id
group by Student.id,Student.name,Student.sex
order by sum(scope)desc

第二种方法：
select subject,SUM(scope) from test group by subject
select student_id,SUM(scope) from test group by student_id

select name,sum(scope) sum_scope from
(
select name,subject,scope,sex from test
left join student on test.student_id=student.id
) ttt group by name
order by sum_scope desc

4.各科排名
select name,subject,scope from test
left join Student on Student.id=test.student_id
group by Student.name,test.subject,test.scope
order by subject,scope asc

5.各科最高分
select subject,max(scope)from test
group by subject

6.优秀率，量率，及格率
select level,COUNT(1)/(select COUNT(1)*1.0 from test) rate from
(
select
name,
subject,
scope,
case sex when 1 then ‘男’ when 2 then ‘女’ else ‘其它’ end sex,
case when scope>=90 then ‘优’ when scope>=70 then ‘良’ when scope>=60 then ‘及格’ else ‘待及格’ end level
from test
left join student on test.student_id=student.id
) test group by level

7.男女分数的比较
select sex,subject,sum(scope) scope from (
select name,subject,scope,
case sex when 1 then ‘男’ else ‘女’ end sex
from test
left join student on test.student_id=student.id
) test
group by sex,subject
order by subject,scope desc

8.各科不及格的人
select name,Student.id,student_id,scope
from
Student,test
where test.student_id=Student.id
and scope<60

9.在86到89或者92到93的女生
第一种：
select
name,
subject,
scope,
sex
from test
left join Student on Student.id = test.student_id
where scope<=86 and scope>=89 or scope>=92 and scope<=93
group by name,subject,sex,scope
having sex = 2

第二种：
select name,subject,scope from test,student
where test.student_id=student.id
and sex=2
and
(
(scope>=86 and scope<=89)
or
(scope>=92 and scope<=93)
)
order by subject

10.偏科严重的同学（排名）
select name,
sum(scope) 总分,
max(scope) 最高分,
min(scope) 最低分,
max(scope)-min(scope) 分差
from (
select name,subject,scope,sex from test
left join student on test.student_id=student.id
) ttt
group by name order by 分差 desc

11.行转列
select
name,
subject,
scope,
sex,
case subject when ‘语文’ then scope else 0 end 语文,
case subject when ‘数学’ then scope else 0 end 数学,
case subject when ‘英语’ then scope else 0 end 英语
from test
left join student on test.student_id=student.id

12.查找最高分，最低分，分差
select
name,SUM(scope) 总分,
AVG(scope) 平均分,
sum(语文) 语文,
sum(数学) 数学,
sum(英语) 英语,
max(scope) 最高分,
min(scope) 最低分,
max(scope)-min(scope) 分差
from(
select
name,
subject,
scope,
sex,
case subject when ‘语文’ then scope else 0 end 语文,
case subject when ‘数学’ then scope else 0 end 数学,
case subject when ‘英语’ then scope else 0 end 英语
from test
left join student on test.student_id=student.id
) ttt
group by name