行转列实现方法
Mysql实现行转列
问题描述:将每门科目作为字段展示每位学生成绩
因为Mysql中是没有POVIT行转列函数的,使用CASE WHEN 方法搭配聚合函数实现
#写法一:使用聚合函数:max()
SELECT
NAME
,CLASS
,MAX(CASE WHEN SUBJECT = '语文' THEN GRADE END) AS '语文'
,MAX(CASE WHEN SUBJECT = '数学' THEN GRADE END) AS '数学'
,MAX(CASE WHEN SUBJECT = '英语' THEN GRADE END) AS '英语'
FROM STUDENTS
GROUP BY NAME
,CLASS
#写法二:使用聚合函数:sum()
SELECT
NAME
,CLASS
,SUM(CASE WHEN SUBJECT = '语文' THEN GRADE END) AS '语文'
,SUM(CASE WHEN SUBJECT = '数学' THEN GRADE END) AS '数学'
,SUM(CASE WHEN SUBJECT = '英语' THEN GRADE END) AS '英语'
FROM STUDENTS
GROUP BY NAME
,CLASS
结果如下:
Oracle实现行转列
问题描述:将每门科目作为字段展示每位学生成绩
GRADE字段,是需要转换的字段
IN后面:将学科内容转换成字段名
POVIT后跟的字段,是需要使用聚合函数的,相当于按照不同学科做汇总
#使用PIVOT()
SELECT * FROM STUDENTS
PIVOT(
SUM(GRADE)
FOR SUBJECT IN ( '语文' AS "语文",'数学' AS "数学",'英语' AS "英语")
)
结果如下: