如何在MySQL里实现行和列的互相转换？

By adminmysql360On 2022年8月13日2022年8月13日

很多时候，我们在开发的时候需要用到SQL行转列或列转行的知识，本文将针对如何在MySQL里实现行和列的互相转换做一些介绍，文中的示例写的还是比较详细的，希望对大家有帮助。

准备测试表数据

假设现在有一张学生成绩表，记录了每个学生不同科目的考试成绩。

1.行转列

现在要进行行转列，达到如下效果，科目由原来的行转为列：

下面就结合该案例介绍几个行转列的方法。

1.1 四种行转列方法

1.1.1 case… when … then … else … end

SELECT
    uname,
    uid,-- 正常查询的字段
    -- 需要转换的字段
    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
    course 
GROUP BY
    uid;

另一种写法：

case course
when '化学' then score
else 0
end

需要注意的是

‘else 0’分支若去掉，那如果某同学没有某课程则分数会显示为null;
sum替换成max结果一样;

1.1.2 if (`column name`=’column value’，，)

SELECT
    uname,
    uid,
    sum( IF ( `course` = '英语', score, 0 ) ) '英语',
    sum( IF ( `course` = '物理', score, 0 ) ) '物理',
    sum( IF ( `course` = '化学', score, 0 ) ) '化学' 
FROM
    course 
GROUP BY
    uname

1和2这两种SQL写法结果是相同的，方法2看着好像比第一种简洁点，下面还有些扩展功能的实现，也是基于该方法来修改的。

1.2 计算每个学生各科的总分和每科所有学生的总分

就像下图，要实现这样的效果。

最右侧的total列实现起来比较简单，在上条SQL的基础上再加个sum(score) 'total'就可以了；

下方的Total行，其实就是上条SQL不加group by的效果。

所以思路到了这儿就简单了，上图中的效果，我们可以看成下面两张图的查询结果的组合，也就是union一下即可。

1.3 if (`column name`=’column value’，，) + union

下面这个sql就是两条查询SQL拼起来的，可以实现上图的效果：

SELECT
    uid,
    uname,
    sum( IF ( `course` = '英语', score, 0 ) ) '英语',
    sum( IF ( `course` = '物理', score, 0 ) ) '物理',
    sum( IF ( `course` = '化学', score, 0 ) ) '化学',
    sum( score ) 'total' 
FROM
    course 
GROUP BY
    uname UNION
SELECT
    'Total',
    NULL,
    sum( IF ( `course` = '英语', score, 0 ) ) '英语',
    sum( IF ( `course` = '物理', score, 0 ) ) '物理',
    sum( IF ( `course` = '化学', score, 0 ) ) '化学',
    sum( score ) 'total' 
FROM
    course

1.4 if (`column name`=’column value’，，) + IFNULL（）+ with rollup

Mysql中有一个with rollup，可以在分组统计数据的基础上再进行统计汇总，即用来得到group by的汇总信息。

说人话就是会在最下面多出一行，这行是对上面统计数据的汇总。

需要注意的是，某列如果不是数字类型，则多出来的最下面一行的该列会显示为null，所以结合本示例，咱可以搭配 fnull()来优化该sql。

SELECT
    ifnull( uid, 'Total' ) uid,
    uname,
    sum( IF ( `course` = '英语', score, 0 ) ) '英语',
    sum( IF ( `course` = '物理', score, 0 ) ) '物理',
    sum( IF ( `course` = '化学', score, 0 ) ) '化学',
    sum( score ) 'total' 
FROM
    course 
GROUP BY
    uid WITH ROLLUP

2.列转行

行转列会了，列转行就简单了，就是按相反的逻辑来可以了，

假设表中的数据是这样的，为了偷懒一下，不创建一个新表，咱可以把上一步查询的SQL改造成一个创建视图的SQL：

CREATE VIEW rtc AS SELECT
ifnull( uid, 'Total' ) uid,
uname,
sum( IF ( `course` = '英语', score, 0 ) ) '英语',
sum( IF ( `course` = '物理', score, 0 ) ) '物理',
sum( IF ( `course` = '化学', score, 0 ) ) '化学',
sum( score ) 'total' 
FROM
    course 
GROUP BY
    uid WITH ROLLUP

该临时表的数据就是这样的：

咱需要的转化后的结果是这样的

下面是列转行代码

SELECT
    uid,
    uname,
    '英语' course,英语 score 
FROM
    rtc 
WHERE
    uid <> 'Total' 
    AND 英语 > 0 UNION ALL
SELECT
    uid,
    uname,
    '物理',物理 
FROM
    rtc 
WHERE
    uid <> 'Total' 
    AND 物理 > 0 UNION ALL
SELECT
    uid,
    uname,
    '化学',化学 
FROM
    rtc 
WHERE
    uid <> 'Total' 
    AND 化学 >0

好了，MySQL里如何实现行列转换都已经介绍完了，希望对你能有帮助。

本文由《MySql教程网》原创，转载请注明出处！https://mysql360.com

浏览量: 319