MySQL数据库纵横表转换

1.纵表：score 目标：切换为横表

SQL语句为：

SELECT name as ‘姓名’,
max(case subject when ‘语文’ THEN value ELSE 0 END)as ‘语文’,
max(case subject WHEN ‘数学’ THEN value else 0 END)as ‘数学’,
max(case subject when ‘英语’ THEN value ELSE 0 END)as ‘英语’
FROM score
GROUP BY name DESC

效果为：

2.横表subject 目标：切换为纵表

SQL语句：

SELECT * FROM(
SELECT 姓名 as name ,‘语文’ as subject,语文 as value FROM subject
UNION
SELECT 姓名 as name ,‘数学’ as subject,数学 as value FROM subject
UNION
SELECT 姓名 as name ,‘英语’ as subject,英语 as value FROM subject
)subject ORDER BY name

效果为：