MySQL动态行转列(列值转换列名)

作者：毕涛涛，致力于Java学习的践行者。原创文章，转载请注明出处。
参考链接：https://blog.csdn.net/wqc19920906/article/details/79791322

一、创建表结构、插入数据

1、三种表：学生表、课程表、分数表

-- 学生表
CREATE TABLE `student` (
    `stuid` VARCHAR(16) NOT NULL COMMENT '学号',
    `stunm` VARCHAR(20) NOT NULL COMMENT '学生姓名',
    PRIMARY KEY (`stuid`)
) COMMENT '学生表';
-- 课程表
CREATE TABLE `courses` (
    `courseno` VARCHAR(20) NOT NULL,
    `coursenm` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`courseno`)
) COMMENT '课程表';
-- 成绩表
CREATE TABLE `score` (
    `stuid` VARCHAR(16) NOT NULL,
    `courseno` VARCHAR(20) NOT NULL,
    `scores` FLOAT NULL DEFAULT NULL,
    PRIMARY KEY (`stuid`, `courseno`)
) COMMENT '成绩表';

2、插入数据

/*学生表数据*/
Insert Into student (stuid, stunm) Values('1001', '张三');
Insert Into student (stuid, stunm) Values('1002', '李四');
Insert Into student (stuid, stunm) Values('1003', '赵二');
Insert Into student (stuid, stunm) Values('1004', '王五');
Insert Into student (stuid, stunm) Values('1005', '刘青');
Insert Into student (stuid, stunm) Values('1006', '周明');

/*课程表数据*/
Insert Into courses (courseno, coursenm) Values('C001', '大学语文');
Insert Into courses (courseno, coursenm) Values('C002', '新视野英语');
Insert Into courses (courseno, coursenm) Values('C003', '离散数学');
Insert Into courses (courseno, coursenm) Values('C004', '概率论与数理统计');
Insert Into courses (courseno, coursenm) Values('C005', '线性代数');
Insert Into courses (courseno, coursenm) Values('C006', '高等数学(一)');

/*成绩表数据*/
Insert Into score(stuid, courseno, scores) Values('1001', 'C001', 67);
Insert Into score(stuid, courseno, scores) Values('1002', 'C001', 68);
Insert Into score(stuid, courseno, scores) Values('1003', 'C001', 69);
Insert Into score(stuid, courseno, scores) Values('1004', 'C001', 70);
Insert Into score(stuid, courseno, scores) Values('1005', 'C001', 71);
Insert Into score(stuid, courseno, scores) Values('1006', 'C001', 72);
Insert Into score(stuid, courseno, scores) Values('1001', 'C002', 87);
Insert Into score(stuid, courseno, scores) Values('1002', 'C002', 88);
Insert Into score(stuid, courseno, scores) Values('1003', 'C002', 89);
Insert Into score(stuid, courseno, scores) Values('1004', 'C002', 90);
Insert Into score(stuid, courseno, scores) Values('1005', 'C002', 91);
Insert Into score(stuid, courseno, scores) Values('1006', 'C002', 92);
Insert Into score(stuid, courseno, scores) Values('1001', 'C003', 83);
Insert Into score(stuid, courseno, scores) Values('1002', 'C003', 84);
Insert Into score(stuid, courseno, scores) Values('1003', 'C003', 85);
Insert Into score(stuid, courseno, scores) Values('1004', 'C003', 86);
Insert Into score(stuid, courseno, scores) Values('1005', 'C003', 87);
Insert Into score(stuid, courseno, scores) Values('1006', 'C003', 88);
Insert Into score(stuid, courseno, scores) Values('1001', 'C004', 88);
Insert Into score(stuid, courseno, scores) Values('1002', 'C004', 89);
Insert Into score(stuid, courseno, scores) Values('1003', 'C004', 90);
Insert Into score(stuid, courseno, scores) Values('1004', 'C004', 91);
Insert Into score(stuid, courseno, scores) Values('1005', 'C004', 92);
Insert Into score(stuid, courseno, scores) Values('1006', 'C004', 93);
Insert Into score(stuid, courseno, scores) Values('1001', 'C005', 77);
Insert Into score(stuid, courseno, scores) Values('1002', 'C005', 78);
Insert Into score(stuid, courseno, scores) Values('1003', 'C005', 79);
Insert Into score(stuid, courseno, scores) Values('1004', 'C005', 80);
Insert Into score(stuid, courseno, scores) Values('1005', 'C005', 81);
Insert Into score(stuid, courseno, scores) Values('1006', 'C005', 82);
Insert Into score(stuid, courseno, scores) Values('1001', 'C006', 77);
Insert Into score(stuid, courseno, scores) Values('1002', 'C006', 78);
Insert Into score(stuid, courseno, scores) Values('1003', 'C006', 79);
Insert Into score(stuid, courseno, scores) Values('1004', 'C006', 80);
Insert Into score(stuid, courseno, scores) Values('1005', 'C006', 81);
Insert Into score(stuid, courseno, scores) Values('1006', 'C006', 82);

二、为什么要行转列

1、查询学生姓名、对应课程以及相应的分数

SELECT
    A.stuid,
    A.stunm,
    B.coursenm,
    C.scores 
FROM
    student A
    INNER JOIN score C ON A.stuid = C.stuid
    INNER JOIN courses B ON C.courseno = B.courseno;
成绩单1.png

2、行转列后的结果

上面是进行成绩查询的时候看到的纵列的结果，但是我们想要的是下面的结果，那么需要这样的结果就要进行 行转列 来操作了。

成绩单2.png

三、实现过程

1、静态行转列

SELECT st.stuid, st.stunm, 
    SUM(CASE c.coursenm WHEN '大学语文' THEN ifnull(s.scores,0) ELSE 0 END ) '大学语文',
    SUM(CASE c.coursenm WHEN '新视野英语' THEN ifnull(s.scores,0) ELSE 0 END ) '新视野英语', 
    SUM(CASE c.coursenm WHEN '离散数学' THEN ifnull(s.scores,0) ELSE 0 END ) '离散数学',
    SUM(CASE c.coursenm WHEN '概率论与数理统计' THEN ifnull(s.scores,0) ELSE 0 END ) '概率论与数理统计',
    SUM(CASE c.coursenm WHEN '线性代数' THEN ifnull(s.scores,0) ELSE 0 END ) '线性代数',
    SUM(CASE c.coursenm WHEN '高等数学(一)' THEN ifnull(s.scores,0) ELSE 0 END ) '高等数学(一)'
FROM student  st
LEFT JOIN score s ON st.stuid = s.stuid
LEFT JOIN courses c ON c.courseno = s.courseno
GROUP BY st.stuid;

看上面的语句可以看出，我们是在知道固定的几门课程之后，可以使用这样的语句来实现行转列。

SUM(CASE c.coursenm WHEN '大学语文' THEN ifnull(s.scores,0) ELSE 0 END ) '大学语文',

\color{red}{这里使用了SUM聚合函数}与后面的GROUP BY 语句结合使用，主要目的是为了消除阶梯状数据。（关联学生表，分数表后，你的结果集会和分数表条数一致）

阶梯状数据.png

但我们都知道，课程不仅仅这几门，如果用上面的语句去写，第一要确定有多少课程，这么多课程的课程名要再拿出来，那样的话写一个查询语句下来，可是要写很多了。那么就想能不能动态进行行转列的操作？答案当然是肯定的了！

2、动态行转列

那么如何进行动态行转列呢？首先我们要动态获取这样的语句

    MAX(CASE c.coursenm WHEN '大学语文' THEN ifnull(s.scores,0) ELSE 0 END ) '大学语文',
    MAX(CASE c.coursenm WHEN '新视野英语' THEN ifnull(s.scores,0) ELSE 0 END ) '新视野英语', 
    MAX(CASE c.coursenm WHEN '离散数学' THEN ifnull(s.scores,0) ELSE 0 END ) '离散数学',

而不是像上面那样一句句写出来，那如何得到这样的语句呢？这里就要用到SQL语句拼接了。具体就是下面的语句：

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(IF(c.coursenm = ''',
      c.coursenm,
      ''', s.scores, 0)) AS ''',
      c.coursenm, ''''
    )
  )
FROM courses c;

得到的结果就是
SQL拼接.png

对，没错，就是我们上面进行 行转列 查询要用的语句，那样就不用知道多少课程和这些课程的名字，只要这样几行代码便可以得到动态的列了。
动态的列是拿到了，那如何再结合SQL语句进行查询得到结果呢？
这里要说明一点，因为用到了拼接函数，如果像上面的查询语句，只是把那几行语句替换掉，也就是下面这样：

SELECT st.stuid, st.stunm, 
(
    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'MAX(IF(c.coursenm = ''',
          c.coursenm,
          ''', s.scores, NULL)) AS ',
          c.coursenm
        )
      )
    FROM courses c
)
FROM student  st
LEFT JOIN score s ON st.stuid = s.stuid
LEFT JOIN courses c ON c.courseno = s.courseno
GROUP BY st.stuid;

然而事与愿违，得到的结果却是这样的
事与愿违的结果.png

这里我就不多做赘述了，想必大家也明白。那么既然这样不行，那该怎么做呢？

没错，这里就要像普通的那些语句那样，进行声明，将语句拼接完整之后，再执行，也就是下面这样：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(IF(c.coursenm = ''',
      c.coursenm,
      ''', s.scores, 0)) AS ''',
      c.coursenm, ''''
    )
  ) INTO @sql
FROM courses c;
 
SET @sql = CONCAT('SELECT st.stuid, st.stunm, ', @sql, 
                        ' FROM Student  st 
                        LEFT JOIN score s ON st.stuid = s.stuid
                        LEFT JOIN courses c ON c.courseno = s.courseno
                        GROUP BY st.stuid');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

直接执行这些语句，得到如下结果:
期望的结果.png

当然这个语句拼接中的查询可以加入条件查询，比如我们要查询学号是1003的成绩
也就是下面这样：


1003学生成绩.png

语句则如下： 
SET @sql = NULL;
SET @stuid = '1003';
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(IF(c.coursenm = ''',
      c.coursenm,
      ''', s.scores, 0)) AS ''',
      c.coursenm, ''''
    )
  ) INTO @sql
FROM courses c;
 
SET @sql = CONCAT('SELECT st.stuid, st.stunm, ', @sql, 
                        ' FROM Student  st 
                        LEFT JOIN score s ON st.stuid = s.stuid
                        LEFT JOIN courses c ON c.courseno = s.courseno
                        WHERE st.stuid = ''', @stuid, '''
                        GROUP BY st.stuid');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

对比前面的语句，我们可以看到在第二行的LEFT JOIN 后面我改了一些，还有就是前面的变量加了一个@stuid [ 注：这里的 @ 符号是在SQL语句定义变量习惯用法，我个人理解应该是用来区分吧！]

像上面的语句，我们如果直接在MySQL中操作是没问题的，但如果用到项目中，那么这个语句显然我们没法用，而且我这次做的项目是结合使用MyBatis，大家都知道在MyBatis中的XML文件中可以自己写SQL语句，但是这样的很显然我们没法放到XML文件中。

而且最关键的是，这里不能用 If 条件，好比我们要判断学号是否为空或者等于0再加上条件进行查询，可是这里不支持。

没错就是下面这样

SET @sql = NULL;
SET @stuid = '1003';
SET @courseno = 'C002';
 
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(IF(c.coursenm = ''',
      c.coursenm,
      ''', s.scores, 0)) AS ''',
      c.coursenm, ''''
    )
  ) INTO @sql
FROM courses c;
 
SET @sql = CONCAT('SELECT st.stuid, st.stunm, ', @sql, 
                        ' From Student  st 
                        LEFT JOIN score s ON st.stuid = s.stuid
                        LEFT JOIN courses c ON c.courseno = s.courseno');
                        
IF @stuid IS NOT NULL AND @stuid != 0 THEN
SET @sql = CONCAT(@sql, ' WHERE st.stuid = ''', @stuid, '''');
END IF;    
 
SET @sql = CONCAT(@sql, ' GROUP BY st.stuid');
 
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

对，我就是加上 if 之后人家就是不支持，就是这么任性。

所以就要用到存储过程啦，而且用存储过程的好处是，方便我们调用，相当于一个函数，其他学生也是类似的查询不需再重复写代码，直接调存储过程就好，还能随心所欲的加上if条件判断，多么美好的事情，哈哈~。

那么说到存储过程，这里该如何写呢？
创建存储过程的语句我就不多写了，这里呢把上面的查询语句直接放到创建存储过程的begin和end直接就可以了，如下：

DELIMITER &&  
drop procedure if exists SP_QueryData;
Create Procedure SP_QueryData(IN stuid varchar(16))
READS SQL DATA 
BEGIN
 
SET @sql = NULL;
SET @stuid = NULL;
 
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(IF(c.coursenm = ''',
      c.coursenm,
      ''', s.scores, 0)) AS ''',
      c.coursenm, ''''
    )
  ) INTO @sql
FROM courses c;
 
SET @sql = CONCAT('SELECT st.stuid, st.stunm, ', @sql, 
                        ' From Student  st 
                        LEFT JOIN score s ON st.stuid = s.stuid
                        LEFT JOIN courses c ON c.courseno = s.courseno');
                        
IF @stuid IS NOT NULL AND @stuid != 0 THEN
SET @sql = CONCAT(@sql, ' WHERE st.stuid = ''', @stuid, '''');
END IF;    
 
SET @sql = CONCAT(@sql, ' GROUP BY st.stuid');
 
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
 
END &&  
DELIMITER ;

嗯，对比上面简单的SQL语句可以看出，这里使用了 if 语句，对学号进行了判断

不过这里要注意一点，这里的if语句不像我们平时Java那种写法，也就是下面

if(条件)
{
    要执行的语句块
}

对，在SQL里面的if语句不一样，不需要括号啊什么的，就像直接说英文一样

IF @stuid is not null and @stuid != 0 then
SET @sql = CONCAT(@sql, ' WHERE st.stuid = ''', @stuid, '''');
END IF;

嗯，就是这么简单明了，如果条件满足，那么就去调用，然后结束。

然后我们就可以传参数调用这个SP了。

CALL `SP_QueryData`('1001');

同样得到我们想要的结果：
期望的结果.png

好了，以上就是这次我在mysql进行动态行转列的实现过程。

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 194,088评论 5赞 459
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 81,715评论 2赞 371
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 141,361评论 0赞 319
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 52,099评论 1赞 263
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 60,987评论 4赞 355
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 46,063评论 1赞 272
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 36,486评论 3赞 381
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 35,175评论 0赞 253
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 39,440评论 1赞 290
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 34,518评论 2赞 309
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 36,305评论 1赞 326
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 32,190评论 3赞 312
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 37,550评论 3赞 298
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 28,880评论 0赞 17
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,152评论 1赞 250
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 41,451评论 2赞 341
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 40,637评论 2赞 335