mysql多列转行

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    math_score INT,
    science_score INT,
    english_score INT
);

SELECT id, name, 'math' AS subject, math_score AS score FROM students
UNION ALL
SELECT id, name, 'science' AS subject, science_score AS score FROM students
UNION ALL
SELECT id, name, 'english' AS subject, english_score AS score FROM students;

CREATE TABLE scores (
    id INT PRIMARY KEY,
    student_id INT,
    subject VARCHAR(50),
    score INT
);

SELECT student_id,
       MAX(CASE WHEN subject = 'math' THEN score END) AS math_score,
       MAX(CASE WHEN subject = 'science' THEN score END) AS science_score,
       MAX(CASE WHEN subject = 'english' THEN score END) AS english_score
FROM scores
GROUP BY student_id;

SELECT DISTINCT id, name, subject, score
FROM (
    SELECT id, name, 'math' AS subject, math_score AS score FROM students
    UNION ALL
    SELECT id, name, 'science' AS subject, science_score AS score FROM students
    UNION ALL
    SELECT id, name, 'english' AS subject, english_score AS score FROM students
) AS temp;

-- 创建索引
CREATE INDEX idx_student_id ON students(id);
CREATE INDEX idx_student_id_subject ON scores(student_id, subject);