编辑代码

CREATE DATABASE test;
use test;
CREATE TABLE Student (
    id INT PRIMARY KEY,      -- 学生的唯一标识符，主键
    name VARCHAR(50),        -- 学生的名字，长度不超过50个字符
    class VARCHAR(50)        -- 学生所在的班级，长度不超过50个字符
);

INSERT INTO Student (id, name, class) VALUES
(1, '赵铁柱', '一班'),
(2, '钱重', '二班'),
(3, '孙紫', '一班'),
(4, '李云龙', '三班'),
(5, '周懂眉', '二班');
 
SELECT * FROM Student;

CREATE TABLE Grades (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- 成绩记录的唯一标识符，主键，自增
    studentId INT NOT NULL,            -- 学生的ID，外键，关联到学生表
    course VARCHAR(50) NOT NULL,       -- 课程名称
    score DECIMAL(5, 2)                -- 成绩，允许两位小数
);

INSERT INTO Grades (studentId, course, score) VALUES
(1, '语文', 89.5),
(1, '数学', 92.0),
(2, '语文', 78.0),
(2, '英语', 85.5),
(3, '数学', 90.0),
(3, '英语', 88.0),
(4, '语文', 76.0),
(4, '数学', 87.0),
(5, '英语', 91.0);

SELECT * FROM Grades;

#
SELECT 
    s.class, 
    AVG(g.score) AS MathAverage
FROM 
    Student s
JOIN 
    Grades g ON s.id = g.studentId
WHERE 
    g.course = '数学'
GROUP BY 
    s.class;

#
SELECT 
    s.class, 
    AVG(CASE WHEN g.course = '语文' THEN g.score ELSE NULL END) AS ChineseAverage,
    AVG(CASE WHEN g.course = '英语' THEN g.score ELSE NULL END) AS EnglishAverage
FROM 
    Student s
JOIN 
    Grades g ON s.id = g.studentId
WHERE 
    s.class NOT IN ('一班', '二班')
GROUP BY 
    s.class;

#
-- SELECT
--     s.class, 
--     s.name, 
--     MAX(CASE WHEN g.course = '数学' THEN g.score ELSE NULL END), 
--     MAX(CASE WHEN g.course = '语文' THEN g.score ELSE NULL END), 
--     MAX(CASE WHEN g.course = '英语' THEN g.score ELSE NULL END),
--     FROM Student s
--     LEFT JOIN 
--     Grades g ON s.id = g.studentId
    
SELECT
    s.class,
    s.name,
    MAX(CASE WHEN g.course = '数学' THEN g.score ELSE NULL END) AS MathScore,
    MAX(CASE WHEN g.course = '语文' THEN g.score ELSE NULL END) AS ChineseScore,
    MAX(CASE WHEN g.course = '英语' THEN g.score ELSE NULL END) AS EnglishScore
FROM Student s
LEFT JOIN Grades g ON s.id = g.studentId
GROUP BY s.id, s.class, s.name;#参数个数只能多，不能少于聚合函数之前的参数个数