数据库表行列转换

行转列

创建表kecheng，并插入数据

 insert into kecheng (id, name, course, score)
 values (1, '张三', '语文', 67);
 insert into kecheng (id, name, course, score)
 values (1, '张三', '数学', 76);
 insert into kecheng (id, name, course, score)
 values (1, '张三', '英语', 43);
 insert into kecheng (id, name, course, score)
 values (1, '张三', '历史', 56);
 insert into kecheng (id, name, course, score)
 values (1, '张三', '化学', 11);
 insert into kecheng (id, name, course, score)
 values (2, '李四', '语文', 54);
 insert into kecheng (id, name, course, score)
 values (2, '李四', '数学', 81);
 insert into kecheng (id, name, course, score)
 values (2, '李四', '英语', 64);
 insert into kecheng (id, name, course, score)
 values (2, '李四', '历史', 93);
 insert into kecheng (id, name, course, score)
 values (2, '李四', '化学', 27);
 insert into kecheng (id, name, course, score)
 values (3, '王五', '语文', 24);
 insert into kecheng (id, name, course, score)
 values (3, '王五', '数学', 25);
 insert into kecheng (id, name, course, score)
 values (3, '王五', '英语', 8);
 insert into kecheng (id, name, course, score)
 values (3, '王五', '历史', 45);
 insert into kecheng (id, name, course, score)
 values (3, '王五', '化学', 1);

查询该表，结构显示：

通过sql查询显示下图：

SQL_A:

select  name,
sum(case when course='语文' then score end) as 语文,
sum(case when course='数学' then score end) as 数学,
sum(case when course='英语' then score end) as 英语,
sum(case when course='历史' then score end) as 历史,
sum(case when course='化学' then score end) as 化学
from kecheng group by name;

SQL_B:

select distinct k.`name` AS name,
(select score from kecheng where name = k.`name` and course = '语文' )as 语文,
(select score from kecheng where name = k.`name` and course = '数学' )as 数学,
(select score from kecheng where name = k.`name` and course = '英语' )as 英语,
(select score from kecheng where name = k.`name` and course = '历史' )as 历史,
(select score from kecheng where name = k.`name` and course = '化学' )as 化学
from kecheng k;

SQL_C:

SELECT name AS name,
SUM(IF(`course`='语文',score,0)) AS 语文,
SUM(IF(`course`='数学',score,0)) AS 数学,
SUM(IF(`course`='英语',score,0)) AS 英语,
SUM(IF(`course`='历史',score,0)) AS 历史,
SUM(IF(`course`='化学',score,0)) AS 化学
FROM kecheng
GROUP BY name;

SQL_A和SQL_B和SQL_C都能达到效果。

列转行

创建表score

添加数据：

insert into score(USER_NAME, CN_SCORE, MATH_SCORE, EN_SCORE) values
("张三", 34, 58, 58),
("李四", 45, 87, 45),
("王五", 76, 34, 89);

SQL:

select user_name, '数学' COURSE, MATH_SCORE as SCORE from score
union all
select user_name, '英语' COURSE, EN_SCORE as SCORE from score
union all
select user_name, '语文' COURSE, CN_SCORE as SCORE from score
order by user_name,COURSE;

效果：