MySQL动态行转列和静态行转列

相关文章推荐
读研的豌豆 · Css ...· 1 年前 ·
温文尔雅的钥匙 · python学习之实现QQ自动发送消息 - 简书· 1 年前 ·
体贴的太阳 · Python中的del用法_51CTO博客_ ...· 1 年前 ·
闷骚的伤疤 · Hashing in SHA512 ...· 1 年前 ·
踢足球的火腿肠 · 斑马打印机ZPL语言编程实战_51CTO博客 ...· 1 年前 ·
静态行转列：
比如说一个订单对应多条数据，当状态（status）=1的时候，数量（num）=25，当状态（status）=2的时候，数量（num）=45，现在想用一条sql记录下不同状态对应的数量为多少，如下图所示：
sql语句示例：
SELECT trades_id,
MAX(CASE WHEN apply_status=1 THEN pay_change ELSE 0 END) 'num_status1',
MAX(CASE WHEN apply_status=2 THEN pay_change ELSE 0 END) 'num_status2'
FROM b_balance_detail
WHERE site_id=100190
AND apply_status!=4 AND trades_id = 1001901531475622312
//上述sql是查询了一个订单的情况，当多个时应当加个group by进行分组查询 如下：
SELECT trades_id,
MAX(CASE WHEN apply_status=1 THEN pay_change ELSE 0 END) 'num_status1',
MAX(CASE WHEN apply_status=2 THEN pay_change ELSE 0 END) 'num_status2'
FROM b_balance_detail 
group by trades_id
#学生表
CREATE TABLE `student` (
    `stuid` VARCHAR(16) NOT NULL COMMENT '学号',
    `stunm` VARCHAR(20) NOT NULL COMMENT '学生姓名',
    PRIMARY KEY (`stuid`)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
#课程表
CREATE TABLE `courses` (
    `courseno` VARCHAR(20) NOT NULL,
    `coursenm` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`courseno`)
COMMENT='课程表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
#成绩表
CREATE TABLE `score` (
    `stuid` VARCHAR(16) NOT NULL,
    `courseno` VARCHAR(20) NOT NULL,
    `scores` FLOAT NULL DEFAULT NULL,
    PRIMARY KEY (`stuid`, `courseno`)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
 以上就是数据库表的结构，没有建立外键，但是可以看到成绩表中关联关系。
数据准备：
/*学生表数据*/
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 courses (courseno, coursenm) Values('C007', '高等数学(二)');
/*成绩表数据*/
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);
 为什么要行转列？？？？
上面是进行成绩查询的时候看到的纵列的结果，但是我们想要的是下面的结果：
那么需要这样的结果就要进行行转列来操作了。
Select st.stuid, st.stunm, 
    MAX(CASE c.coursenm WHEN '大学语文' THEN s.scores 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 ) '离散数学',
    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 ) '高等数学(一)',
    MAX(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
看上面的语句可以看出，我们是在知道固定的几门课程之后，可以使用：
MAX(CASE c.coursenm WHEN '线性代数' THEN ifnull(s.scores,0) ELSE 0 END ) '线性代数',
这样的语句来实现行转列，也就是我们一开始所说的的静态行转列示例。
但我们都知道，课程不仅仅这几门，如果用上面的语句去写，第一要确定有多少课程，这么多课程的课程名要再拿出来，那样的话写一个查询语句下来，可是要写很多了。那么就想能不能动态进行行转列的操作？答案当然是肯定的了！
那么接下来才是我们的想要引出的话题“动态行转列”，并且亲测有效：
那么如何进行动态行转列呢？
首先我们要动态获取这样的语句：
MAX(CASE c.coursenm WHEN '大学语文' THEN s.scores 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(
      'MAX(IF(c.coursenm = ''',
      c.coursenm,
      ''', s.scores, 0)) AS ''',
      c.coursenm, ''''
FROM courses c;
得到的结果就是：
MAX(IF(c.coursenm = '大学语文', s.scores, 0)) AS '大学语文',
MAX(IF(c.coursenm = '新视野英语', s.scores, 0)) AS '新视野英语',
MAX(IF(c.coursenm = '离散数学', s.scores, 0)) AS '离散数学',
MAX(IF(c.coursenm = '概率论与数理统计', s.scores, 0)) AS '概率论与数理统计',
MAX(IF(c.coursenm = '线性代数', s.scores, 0)) AS '线性代数',
MAX(IF(c.coursenm = '高等数学(一)', s.scores, 0)) AS '高等数学(一)',
MAX(IF(c.coursenm = '高等数学(二)', s.scores, 0)) AS '高等数学(二)'
对，没错，就是我们上面进行行转列查询要用的语句，那样就不用知道多少课程和这些课程的名字，只要这样几行代码便可以得到动态的列了。
group_concat()，手册上说明:该函数返回带有来自一个组的连接的非NULL值的字符串结果。
通俗点理解，其实是这样的：group_concat()会计算哪些行属于同一组，将属于同一组的列显示出来。要返回哪些列，由函数参数(就是字段名)决定。分组必须有个标准，就是根据group by指定的列进行分组。
concat() 操作的参数是数组，那么添加的是数组中的元素，而不是数组。该方法不会改变现有的数组，而仅仅会返回被连接数组的一个副本
动态的列是拿到了，那如何再结合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;
 然而得到的结果却是这样的：
这里我就不多做赘述了，想必大家也明白。那么既然这样不行，那该怎么做呢？
没错，这里就要像普通的那些语句那样，进行声明，将语句拼接完整之后，再执行，也就是下面这样
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(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;
直接执行这些语句，得到如下结果。
MySQL prepare语法： 
PREPARE statement_name FROM preparable_SQL_statement; /*定义*/ 
EXECUTE statement_name [USING @var_name [, @var_name] ...]; /*执行预处理语句*/ 
{DEALLOCATE | DROP} PREPARE statement_name /*删除定义*/ ; 
当然这个语句拼接中的查询可以加入条件查询，比如我们要查询学号是1003的成绩
也就是下面这样
语句则如下
SET @sql = NULL;
SET @stuid = '1003';
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(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(
      'MAX(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(
      'MAX(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 <> '' then
SET @stuid = stuid;
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;
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;    
嗯，就是这么简单明了，如果条件满足，那么就怎么样，然后结束。
然后我们就可以传参数调用这个存储过程了：
CALL `SP_QueryData`('1001');
得到如下结果
当然我们也可以直接传个空串过去：
CALL `SP_QueryData`(' ');
同样得到我们想要的结果：
好了，以上就是这次我在MYSQL进行动态行转列的实现过程。
遇到的问题总结：
哦哦，最后顺带提及一句，我写这个的时候发现Mysql的concat或者concat_ws函数里可以case when，而group_concat函数里case when做判断会报错！
还有个问题，就是存储过程里有一段代码：
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
如果不让Mysql预加载执行的话存储过程不起效果！