合 MySQL中的行转列
👉 本文共约194个字，系统预计阅读时间或需1分钟。
环境准备
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | CREATE TABLE scores ( id INT, subject VARCHAR(50), score INT ); INSERT INTO scores (id, subject, score) VALUES (1, 'Math', 80), (1, 'Science', 90), (2, 'Math', 75), (2, 'Science', 85), (3, 'Math', 90), (3, 'Science', 95); |
max+case函数
1 2 3 4 5 6 | SELECT id, MAX(CASE WHEN subject = 'Math' THEN score END) AS Math, MAX(CASE WHEN subject = 'Science' THEN score END) AS Science FROM scores GROUP BY id; |
使用if函数
1 2 3 4 5 6 | SELECT id, sum(IF(subject = 'Math', score, NULL)) AS Math, sum(IF(subject = 'Science', score, NULL)) AS Science FROM scores GROUP BY id; |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
