MySQL表结构变化

• 建表SQL

CREATE TABLE TableA ( 
Name VARCHAR(20), 
Course VARCHAR(20),
Grade INT 
); 
INSERT INTO TableA (Name, Course, Grade) 
VALUES ('张三', '语文', 75),
('张三', '数学', 80), 
('张三', '英语', 90), 
('李四', '语文', 95), 
('李四', '数学', 55);
CREATE TABLE TableB ( 
Name VARCHAR(20), 
语文 INT, 
数学 INT, 
英语 INT
);

INSERT INTO TableB (Name, 语文, 数学, 英语) 
SELECT 
Name, 
MAX(CASE WHEN Course = '语文' THEN Grade ELSE 0 END) AS 语文,
MAX(CASE WHEN Course = '数学' THEN Grade ELSE 0 END) AS 数学, 
MAX(CASE WHEN Course = '英语' THEN Grade ELSE 0 END) AS 英语 
FROM temp 
GROUP BY Name; 
DROP TABLE temp;