编辑代码

CREATE DATABASE test;
use test;
CREATE TABLE t_score(
    sid     INT,
    sname   VARCHAR(20),
    subject VARCHAR(20),
    score   INT
);

INSERT INTO t_score(sid, sname, subject, score) VALUES
(111, 'kilo', 'chinese', 75),
(111, 'kilo', 'math',    85),
(111, 'kilo', 'english', 95),
(112, 'lima', 'chinese', 82),
(112, 'lima', 'math',    83),
(112, 'lima', 'english', 81),
(113, 'mike', 'chinese', 78),
(113, 'mike', 'math',    96),
(113, 'mike', 'english', 78);

-- 1.找出每科成绩都在80分以上的学生姓名;
select sname
from t_score
group by sid,sname
having min(score) > 80;

-- 2.请找出每个同学分数最高的学科
select sid,sname,subject
from(
SELECT sid, sname, subject, score,
       RANK() OVER (PARTITION BY sid ORDER BY score DESC) AS ranka
FROM t_score) as t
where ranka =1;

/*3.将学生成绩表转置，结果如下
subject|kilo|lima|mike*/
select subject,
max(case when sname = 'kilo' then score end) as kilo,
max(case when sname = 'lima' then score end) as lima,
max(case when sname = 'mike' then score end) as mike
from t_score
group by subject;

/* 4.将每个学生的各科成绩合并成一行，结果如下：kilo  chinese:75,english:95,math:85*/
select sname,
    group_concat(concat(subject,':',score) order by subject separator ',') as subject_score
from t_score
group by sid,sname;