1.sum(case when c_id='01' then s_score else NULL end ) cid1,
加行记录为单列值,该新列和值
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(难)
-- 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(难)
SELECT s_id,AVG(s_score) FROM score GROUP BY s_id ORDER BY AVG(s_score) DESC;#平均成绩从高到低显示所有学生的平均成绩
#sum作用加行记录
SELECT s_id,
sum(case when c_id='01' then s_score else NULL end ) cid1,
sum(case when c_id='02' then s_score else NULL end ) cid2,
sum(case when c_id='03' then s_score else NULL end ) cid3,
AVG(s_score) FROM score GROUP BY s_id ORDER BY AVG(s_score) DESC;
#sum作用加行记录
select student.s_id,
sum(case when c_id='01' then s_score else NULL end ) cid1,
sum(case when c_id='02' then s_score else NULL end ) cid2,
sum(case when c_id='03' then s_score else NULL end ) cid3,
avg(s_score) average
from student,score
where student.s_id=score.s_id
group by student.s_id
order by average desc;
select student.s_id,
(case when c_id='01' then s_score else NULL end ) cid1,
(case when c_id='02' then s_score else NULL end ) cid2,
(case when c_id='03' then s_score else NULL end ) cid3,
avg(s_score) average
from student,score
where student.s_id=score.s_id
group by student.s_id
order by average desc;
2及格率等等sum(case when s_score>=60 then 1 else 0 end)/count(s_score) '及格率'
18、查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
select score.c_id,course.c_name,max(s_score),min(s_score),avg(s_score),
sum(case when s_score>=60 then 1 else 0 end)/count(s_score) '及格率',
sum(case when s_score>=90 then 1 else 0 end)/count(s_score) '优秀率'
from score,course where score.c_id=course.c_id
group by score.c_id;
-- 按sid 查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
SELECT score.s_id,MAX(s_score),MIN(s_score),AVG(s_score),
SUM(CASE WHEN s_score>=60 THEN 1 ELSE 0 END)/COUNT(s_id) "及格率",
SUM(CASE WHEN s_score>=90 THEN 1 ELSE 0 END)/COUNT(s_id) "优秀率"
FROM score,course WHERE score.c_id = course.c_id GROUP BY score.s_id;
3平均成绩名次
笛卡尔积cross join
SELECT * FROM course CROSS JOIN student
sum(case when s_score>0 then s_score else 0 end)/count(a.c_id)
空记录作为0运算
21、查询学生平均成绩及其名次
row_number() over(order by savg desc) AS rank_cid
对savg字段标序号并查询出列值
#平均成绩按3门
SELECT s_name,s_id,savg,
row_number() over(order by savg desc) AS rank_cid FROM(
#平均成绩按所有课程
SELECT s_name,a.s_id,
sum(case when s_score>0 then s_score else 0 end)/count(a.c_id) AS savg
FROM (SELECT * FROM course CROSS JOIN student) AS a
LEFT JOIN score AS s ON a.c_id = s.c_id AND a.s_id = s.s_id
GROUP BY a.s_id
) as b;
SELECT s_id,AVG(s_score) FROM score GROUP BY s_id ORDER BY SUM(s_score) DESC; #总成绩下排名
SELECT s_id,AVG(s_score) FROM score GROUP BY s_id ORDER BY AVG(s_score) DESC;#平均成绩下排名 有的两门
-- 序号问题
#row_number 不重复123, rank 存在相同排名重复113, dense_rank 相同排名重复 112
select s_id,c_id,s_score,
row_number() over(order by s_score desc) rank_cid #只给成绩标序号
from score ORDER BY s_score;select s_id,c_id,s_score,
rank() over(order by s_score desc) rank_cid #只给成绩标序号
from score ORDER BY s_score;
select s_id,c_id,s_score,
dense_rank() over(order by s_score desc) rank_cid #只给成绩标序号
from score ORDER BY s_score;
4分组排序partition by c_id
row_number() over(partition by c_id order by s_score desc) rank_cid
select s_id,c_id,s_score,
row_number() over(partition by c_id order by s_score desc) rank_cid #按课程分组给序号
from score ORDER BY s_id;
select s_id,c_id,s_score,
row_number() over(order by s_score desc) rank_cid #只给成绩标序号
from score ORDER BY s_id;
5自连接
41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 (难)
SELECT DISTINCT a.* FROM score a
INNER JOIN score b ON
a.s_id = b.s_id
AND a.c_id != b.c_id
AND a.s_score = b.s_score
6日期相关,年龄TIMESTAMPDIFF(year,b,n)
年龄:TIMESTAMPDIFF(year,s_birth,NOW()) age
SELECT YEAR("1990-01-01");
SELECT YEAR(NOW());
SELECT MONTH(NOW());#一年之内
SELECT WEEK(NOW());#一年之内
7分段case when then else写法
SELECT *,CASE
WHEN s_score<60 THEN "D[<60]"
WHEN s_score>=60 AND s_score<70 THEN "C[70-60]"
WHEN s_score>=70 AND s_score<85 THEN "B[85-70]"
WHEN s_score>=85 AND s_score<100 THEN "A[100-85]"
ELSE "不合法" END AS "成绩情况"
FROM(
分段可以解决union需要多个select语句情况
8插入-进阶版
insert into tablename (cols) select .....
可加union
字段对应,自增可略该字段
insert into score values("10","10",90);
#除了自动增长的字段auto_increment
insert into teacher (t_name) SELECT s_name from student WHERE s_id="01";