创建数据库
CREATE DATABASE `ceshi`CHARACTER SET utf8 COLLATE utf8_general_ci;
建表
CREATE TABLE exampleTable (course VARCHAR(20),teacher VARCHAR(20),student VARCHAR(20),score INT) ;
插入数据
INSERT INTO exampleTable (course,teacher,student,score) VALUES ('数学',' 王华','丽丽',60);
INSERT INTO exampleTable (course,teacher,student,score) VALUES ('数学',' 王华','李四',30);
INSERT INTO exampleTable (course,teacher,student,score) VALUES ('语文',' 张三','丽丽',70);
INSERT INTO exampleTable (course,teacher,student,score) VALUES ('语文',' 张三','李四',80);
SELECT student,
SUM(CASE course WHEN '数学' THEN score ELSE 0 END) AS '数学' ,
SUM(CASE course WHEN '语文' THEN score ELSE 0 END) AS '语文'
FROM exampleTable GROUP BY student;
结果