mysql行转列解析

Love The Way You Lie 2021-12-19 23:05 293阅读 0赞

场景：mysq行列转换，把个人和科目成绩的情况放在一行展示，方便查看个人情况。（sql来说就是把一个重复列以及其对应关系的列，变成一行展示）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbnQ2_size_16_color_FFFFFF_t_70][]===》![20190630235353170.png][]

sql测试语句

DROP TABLE IF EXISTS `t_score`;
    CREATE TABLE `t_score` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) CHARACTER SET utf8 NOT NULL,
      `Subject` varchar(10) CHARACTER SET utf8 NOT NULL,
      `Fraction` double DEFAULT '0',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of t_score
    -- ----------------------------
    INSERT INTO `t_score` VALUES ('1', '王海', '语文', '86');
    INSERT INTO `t_score` VALUES ('2', '王海', '数学', '83');
    INSERT INTO `t_score` VALUES ('3', '王海', '英语', '93');
    INSERT INTO `t_score` VALUES ('4', '陶俊', '语文', '88');
    INSERT INTO `t_score` VALUES ('5', '陶俊', '数学', '84');
    INSERT INTO `t_score` VALUES ('6', '陶俊', '英语', '94');
    INSERT INTO `t_score` VALUES ('7', '刘可', '语文', '80');
    INSERT INTO `t_score` VALUES ('8', '刘可', '数学', '86');
    INSERT INTO `t_score` VALUES ('9', '刘可', '英语', '88');
    INSERT INTO `t_score` VALUES ('10', '李春', '语文', '89');
    INSERT INTO `t_score` VALUES ('11', '李春', '数学', '80');
    INSERT INTO `t_score` VALUES ('12', '李春', '英语', '87');
    INSERT INTO `t_score` VALUES ('13', '王海', '物理', '4');

这样写转换sql

select 
      `name`,
           sum(if(Subject='语文',Fraction,0)) as 语文,
           sum(if(Subject='英语',Fraction,0)) as 英语,
           sum(if(Subject='数学',Fraction,0))as 数学
            from t_score group by name ;

先展示数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbnQ2_size_16_color_FFFFFF_t_70 1][]

转换逻辑分析，sql先根据group by name分组，比如王海分组后是

**1.name：王海，Subject 合并，Fraction合并**

![20190701001432270.png][]

**2.if(Subject='语文',Fraction,0)  遍历。subject 判断当前科目是不是语文，等于的话输出Fraction对应的值，不等于的话输出0**，

以语文为例，语文与王海的其他科目相比，按顺序：语文与语文，语文与数学，语文与英语，语文与物理

依次为86，0，0，0

**3.sum(if(Subject='语文',Fraction,0)) as 语文, 然后把所有的结果相加等于 86**

![20190701001845542.png][]

**4.所以就会有这样的情况**，

（1）比如语文有多个

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbnQ2_size_16_color_FFFFFF_t_70 2][]===》语文与语文，语文与数学，语文与英语，语文语文

结果为，86，0，0，4   所以相加为90

![2019070100235034.png][]

（2）sum(if(Subject='语文',Fraction,1000))不存在默认为1000  会导致正常结果增加

select 
      `name`,
           sum(if(Subject='语文',Fraction,1000)) as 语文,
           sum(if(Subject='英语',Fraction,0)) as 英语,
           sum(if(Subject='数学',Fraction,0))as 数学
            from t_score group by name ;

结果：语文都增加了2000

![20190701002536552.png][]

分析，王海的，语文与语文，语文与数学，语文与英语，语文语文   结果为 86，1000，1000，4  所以相加2090

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbnQ2_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20190630235250216.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbnQ2,size_16,color_FFFFFF,t_70
[20190630235353170.png]: /images/20211219/d286f7f74def4c8492082e53f4902326.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbnQ2_size_16_color_FFFFFF_t_70 1]: /images/20211219/82f9898097a94c30bfa762dd1ecd82f6.png
[20190701001432270.png]: /images/20211219/0dc82a3baf93436c95fd0587d3199a10.png
[20190701001845542.png]: /images/20211219/5f145bda05804406a3f2795ae7e8b0c9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbnQ2_size_16_color_FFFFFF_t_70 2]: /images/20211219/c125171848d54401974b5b031c134fc1.png
[2019070100235034.png]: /images/20211219/b897f78190c54b6190af368fd793ffd6.png
[20190701002536552.png]: /images/20211219/2beef6f7283f411da4b08d374aad46ff.png