USE test;
CREATE TABLE student(
id VARCHAR(20) NOT NULL,
subjects VARCHAR(20) NOT NULL,
grade INTEGER NOT NULL DEFAULT 0
);
INSERT INTO student VALUES('student1','语文',80);
INSERT INTO student VALUES('student1','数学',70);
INSERT INTO student VALUES('student1','英语',60);
INSERT INTO student VALUES('student2','语文',90);
INSERT INTO student VALUES('student2','数学',80);
INSERT INTO student VALUES('student2','英语',100);
COMMIT;
SELECT * FROM student;
id subjects grade
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
/* 行列转换*/
SELECT
id,
SUM(IF(subjects='语文',grade,NULL)) '语文',
SUM(IF(subjects='数学',grade,NULL)) '数学',
SUM(IF(subjects='英语',grade,NULL)) '英语'
FROM
student
GROUP BY
id
;
id 语文 数学 英语
student1 80 70 60
student2 90 80 100