根据你描述,我觉得额,你可能是想知道,行如何转列,列如何转行吧。
行转列
表如下:
(1)行转列(利用最大值)
select user_name,
MAX(CASE WHEN course=‘语文’ THEN score ELSE 0 END) 语文,
MAX(CASE WHEN course=‘英语’ THEN score ELSE 0 END) 英语,
MAX(CASE WHEN course=‘数学’ THEN score ELSE 0 END) 数学
from rows_test GROUP BY user_name
(2)行转列(利用sum总和)
select user_name,
SUM(CASE WHEN course=‘语文’ THEN score ELSE 0 END) 语文,
SUM(CASE WHEN course=‘英语’ THEN score ELSE 0 END) 英语,
SUM(CASE WHEN course=‘数学’ THEN score ELSE 0 END) 数学
from rows_test GROUP BY user_name
(3)行转列(行合并)
select user_name,GROUP_CONCAT(course,":",score) from rows_test GROUP BY user_name
列转行
select user_name, ‘英语’ COURSE , EN_SCORE as SCORE from column_test
union select user_name, ‘语文’ COURSE, CN_SCORE as SCORE from column_test
union select user_name, ‘数学’ COURSE, MATH_SCORE as SCORE from column_test
order by user_name,COURSE;
整理了一个比较简单的案例方法,学到了吗?