sql 转置_SQL -- 练习汇总
一、SQL知识相关应用
涉及表如下：
（一）简单查询
1、查询姓“猴”的学生名单。
select * from student where 姓名 like '猴%'；2、查询姓名中最后一个字“猴”的学生名单。
select * from student where 姓名 like '%猴'；3、查询姓名中带“猴”的学生名单。
select * from student where 姓名 like '%猴%';4、查询姓“孟”老师的个数。
select count(教师号) from teacher where 教师姓名 like '孟%';（二）汇总分析
1、查询课程编号为“0002”的总成绩。
select sum(成绩) from score where 课程号='0002';2、查询选了课程的学生人数。
select count(distinct 学号) as 学生人数 from score;3、查询各科成绩最高和最低分的分。
select 课程号,max(成绩)as 最高分,min(成绩) as 最低分 from score group by 课程号;4、查询每门课程被选修的学生数。【学号作为主键，默认具有唯一约束特性】
select 课程号,count(学号) from score group by 课程号;5、查询男生、女生人数。【性别分组，直接对分组项计数】
select 性别,count(*) from student group by 性别;6、查询平均成绩大于60分学生的学号和平均成绩。
select 学号,avg(成绩) from score group by 学号 having avg(成绩)>60;7、查询至少选修两门课程的学生学号。【对课程号计数】
select 学号,count(课程号) as 选课数 from score group by 学号 having count(课程号)>=2;8、查询同名同姓学生名单并统计同名人数。【对姓名计数】
select 姓名,count(姓名) as 同名同姓数量 from student group by 姓名 having count(姓名)>1;9、查询不及格的课程并按课程号从大到小排列。
select 课程号 from score where 成绩<60 order by 课程号 desc;10、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列。
select 课程号,avg(成绩) as 平均成绩 from score group by 课程号 order by avg(成绩) asc,课程号 desc;11、检索课程编号为“0004”且分数小于60的学生学号，结果按分数降序排列。
select 学号 from score where 课程号='0004' and 成绩<60 order by 成绩 desc;12、统计每门课程的学生选修人数（超过2人的课程才统计）。
要求：结果输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排列。
select 课程号,count(学号) from score group by 课程号 having count(学号)>2
order by count(学号) desc,课程号 asc;13、查询两门及以上不及格课程的同学的学号，以及不及格课程的平均成绩。
select 学号,avg(成绩) as 平均成绩 from score where 成绩<60 group by 学号 having count(成绩)>=2;（三）复杂查询
1、查询所有课程成绩小于60分学生的学号、姓名。 【最高成绩小于60】
select 学号,姓名 from student where 学号 in (select 学号 from score group by 学号 having max(成绩)<60);2、查询没有学全所有课的学生的学号、姓名。【三表串联】
select 学号,姓名 from student where 学号 in (select 学号 from score
group by 学号 having count(学号) <(select count(课程号) from course);3、查询出只选修了两门课程的全部学生的学号和姓名。【学号分组，in】
select 学号,姓名 from student where 学号 in (select 学号 from score group by 学号 having count(课程号)=2);4、1990年出生的学生名单。
select 学号,姓名 from student where year(出生日期) = '1990';5、常见问题：如何找到每个类别下用户最喜欢的产品是哪个？如何每个类别下用户点击最多的5个商品是什么？
按课程号分组取得成绩最大值所在行的数据。【内外课程号一致】
select * from score as a where 成绩 = (select max(成绩) from score as b where a.课程号=b.课程号 group by 课程号)； 6、按课程号分组取得成绩最小值所在行的数据。
select * from score as a where 成绩 = (select min(成绩) from score as b where a.课程号=b.课程号 group by 课程号);7、查询各科成绩前两名的记录。 【order by/limit 最后执行】
(select * from score where 课程号='0001' order by 成绩 desc limit 2)
union all
(select * from score where 课程号='0002' order by 成绩 desc limit 2)
union all
(select * from score where 课程号='0003' order by 成绩 desc limit 2);（四）多表查询
1、不是近视的学生都有谁？学生表：学号，姓名；近视表：序号，学生学号。
select a.姓名 as 不近视名单 from 学生表 as a left join 近视表 as b on a.学号=b.学生学号 where b.学生学号 is null;2、查询所有学生的学号、姓名、选课数、总成绩。【学号分组】
select a.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩 from student as a left join score as b
on a.学号=b.学号 group by a.学号;3、查询平均成绩大于85的所有学生的学号、姓名和平均成绩。【学号分组】
select a.学号,a.姓名,avg(b.成绩) as 平均成绩 from student as a left join score as b
on a.学号=b.学号 group by a.学号 having avg(b.成绩)>85;4、查询学生的选课情况：学号、姓名，课程号，课程名称。
select a.学号,a.姓名,c.课程号,c.课程名称 from student as a inner join
score as b on a.学号=b.学号 inner join course as c on b.课程号=c.课程号；5、查询出每门课程的及格人数和不及格人数。【case】
select 课程号,sum(case when 成绩>=60 then 1 else 0 end) as 及格人数,
sum(case when 成绩<60 then 1 else 0 end) as 不及格人数 from score group by 课程号;6、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数,课程号和课程名称。【right join ，group by】
select a.课程号,b.课程名称,
sum(case when 成绩 =<100 and 成绩>=85 then 1 else 0 end) as '[100-85]',
sum(case when 成绩 <85 and 成绩>=70 then 1 else 0 end) as '[85-70]',
sum(case when 成绩 <70 and 成绩>=60 then 1 else 0 end) as '[70-60]',
sum(case when 成绩 <60 then 1 else 0 end) as '[<60]',
from score as a right join course as b on a.课程号=b.课程号 group by a.课程号,b.课程名称; 7、查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名。
select 学号,姓名 from student as a inner join score as b on a.学号=b.学号
where b.课程号='0003'and b.成绩>80;8、用sql实现成绩表(score)的转置。
select 学号,max(case 课程号 when '0001' then 成绩 else 0 end ) as '课程号0001',
max(case 课程号 when '0002' then 成绩 else 0 end )'课程号0002',
max(case 课程号 when '0003' then 成绩 else 0 end )'课程号0003'
from score group by 学号;二、SQL：练习汇总逻辑图
