MySQL 行转列详情

目录
  • 一、行转列SQL写法
  • 二、如果领导@你,让你在结果集中加上总数列呢?
  • 三、领导又双叒叕@你改需求
  • 四、结束语
  • 附录:创建表结构&测试数据SQL

  MySQL行转列,对经常处理数据的同学们来说,一定是不陌生的,甚至是印象深刻,因为它大概率困扰过你,让你为之一愣~ 但当你看到本文后,这个问题就不在是问题,及时收藏,以后谁再问你这个问题,直接甩他脸上,粘贴即用。

首先,我们看一下咱们的测试表数据和预期查询的结果:

mysql> SELECT * FROM t_gaokao_score;
+----+--------------+--------------+-------+
| id | student_name | subject      | score |
+----+--------------+--------------+-------+
|  1 | 林磊儿       | 语文         |   148 |
|  2 | 林磊儿       | 数学         |   150 |
|  3 | 林磊儿       | 英语         |   147 |
|  4 | 乔英子       | 语文         |   121 |
|  5 | 乔英子       | 数学         |   106 |
|  6 | 乔英子       | 英语         |   146 |
|  7 | 方一凡       | 语文         |    70 |
|  8 | 方一凡       | 数学         |    90 |
|  9 | 方一凡       | 英语         |    59 |
| 10 | 方一凡       | 特长加分     |   200 |
| 11 | 陈哈哈       | 语文         |   109 |
| 12 | 陈哈哈       | 数学         |    92 |
| 13 | 陈哈哈       | 英语         |    80 |
+----+--------------+--------------+-------+
13 rows in set (0.00 sec)

看看我们行转列转完后的结果:

+--------------+--------+--------+--------+--------------+
| student_name | 语文   | 数学   | 英语   | 特长加分     |
+--------------+--------+--------+--------+--------------+
| 林磊儿       |    148 |    150 |    147 |            0 |
| 乔英子       |    121 |    106 |    146 |            0 |
| 方一凡       |     70 |     90 |     59 |          200 |
| 陈哈哈       |    109 |     92 |     80 |            0 |
+--------------+--------+--------+--------+--------------+
4 rows in set (0.00 sec)

  好,下面我们一起来看看SQL是如何编写的,对了,创建表结构和导入测试数据的SQL放到文章末尾了,自取~

一、行转列SQL写法

方法一、使用case..when..then进行 行转列

ELECT student_name,
    SUM(CASE `subject` WHEN '语文' THEN score ELSE 0 END) as '语文',
    SUM(CASE `subject` WHEN '数学' THEN score ELSE 0 END) as '数学',
    SUM(CASE `subject` WHEN '英语' THEN score ELSE 0 END) as '英语',
    SUM(CASE `subject` WHEN '特长加分' THEN score ELSE 0 END) as '特长加分' 
FROM t_gaokao_score 
GROUP BY student_name;

  这里如果不使用SUM()会报sql_mode=only_full_group_by相关错误,需要聚合函数和group by连用或使用distinct才可以解决。

  其实,加了SUM()是为了能够使用GROUP BY根据student_name进行分组,每一个student_name对应的subject="语文"的记录毕竟只有一条,所以SUM() 的值就等于对应那一条记录的score的值。当然,也可以换成MAX()。

方法二、使用IF()进行 行转列:

ELECT student_name,
    SUM(IF(`subject`='语文',score,0)) as '语文',
    SUM(IF(`subject`='数学',score,0)) as '数学',
    SUM(IF(`subject`='英语',score,0)) as '英语',
    SUM(IF(`subject`='特长加分',score,0)) as '特长加分' 
FROM t_gaokao_score 
GROUP BY student_name;

  该方法将IF(subject='语文',score,0)作为条件,通过student_name进行分组,对分组后所有subject='语文’的记录的score字段进行SUM()操作,如果score没有值则默认为0。

  这种方式和case..when..then方法原理相同,相比更加简洁明了,建议使用。

二、如果领导@你,让你在结果集中加上总数列呢?

友情提示:我们工作中处理行转列数据时,尽量都把总数、平均数等加上,方便领导查阅,省得他循环BB你。

话说,你还记得上学时的成绩表是啥样的么?你一般从上往下看还是从下往上看呢?文末投票,快来给大家乐呵乐呵!

写法:利用SUM(IF()) 生成列,WITH ROLLUP 生成汇总列和行,并利用 IFNULL将汇总行标题显示为总数

SELECT IFNULL(student_name,'总数') AS student_name,
    SUM(IF(`subject`='语文',score,0)) AS '语文',
    SUM(IF(`subject`='数学',score,0)) AS '数学',
    SUM(IF(`subject`='英语',score,0)) AS '英语',
    SUM(IF(`subject`='特长加分',score,0)) AS '特长加分',
    SUM(score) AS '总数' 
FROM t_gaokao_score
GROUP BY student_name WITH ROLLUP;

查询结果:

+--------------+--------+--------+--------+--------------+--------+
| student_name | 语文   | 数学   | 英语   | 特长加分     | 总数   |
+--------------+--------+--------+--------+--------------+--------+
| 乔英子       |    121 |    106 |    146 |            0 |    373 |
| 方一凡       |     70 |     90 |     59 |          200 |    419 |
| 林磊儿       |    148 |    150 |    147 |            0 |    445 |
| 陈哈哈       |    113 |    116 |     80 |            0 |    309 |
| 总数         |    452 |    462 |    432 |          200 |   1546 |
+--------------+--------+--------+--------+--------------+--------+
5 rows in set, 1 warning (0.00 sec)

三、领导又双叒叕@你改需求

  让你把分值转化为具体内容显示(优秀、良好、普通、差),430分以上重点大学,400分以上一本,350分及以上二本,350以下搬砖,该怎么写呢?

  这里我们就需要case when嵌套一下了,看着高大上,其实就是普通的嵌套而已。在第一层查出分组后的各科分数,在第二层替换成等级即可。

SELECT student_name,
MAX(  
        CASE subject  
        WHEN '语文' THEN  
            (  
                CASE  
                WHEN score - (select avg(score) from t_gaokao_score where subject='语文') > 20 THEN  
                    '优秀'  
                WHEN score - (select avg(score) from t_gaokao_score where subject='语文') > 10 THEN  
                    '良好'  
                WHEN score - (select avg(score) from t_gaokao_score where subject='语文') >= 0 THEN  
                    '普通'  
                ELSE  
                    '差'  
                END  
            )  
        END  
    ) as '语文', 
MAX(  
        CASE subject  
        WHEN '数学' THEN  
            (  
                CASE  
                WHEN score - (select avg(score) from t_gaokao_score where subject='数学') > 20 THEN  
                    '优秀'  
                WHEN score - (select avg(score) from t_gaokao_score where subject='数学') > 10 THEN  
                    '良好'  
                WHEN score - (select avg(score) from t_gaokao_score where subject='数学') >= 0 THEN  
                    '普通'  
                ELSE  
                    '差'  
                END  
            )  
        END  
    ) as '数学',
MAX(  
        CASE subject  
        WHEN '英语' THEN  
            (  
                CASE  
                WHEN score - (select avg(score) from t_gaokao_score where subject='英语') > 20 THEN  
                    '优秀'  
                WHEN score - (select avg(score) from t_gaokao_score where subject='英语') > 10 THEN  
                    '良好'  
                WHEN score - (select avg(score) from t_gaokao_score where subject='英语') >= 0 THEN  
                    '普通'  
                ELSE  
                    '差'  
                END  
            )  
        END  
    ) as '英语',
SUM(score) as '总分',
(CASE WHEN SUM(score) > 430 THEN '重点大学'  
      WHEN SUM(score) > 400 THEN '一本'  
      WHEN SUM(score) > 350 THEN '二本'  
      ELSE '工地搬砖' 
      END ) as '结果'
FROM t_gaokao_score 
GROUP BY student_name 
ORDER BY SUM(score) desc;

我们来看一下输出结果:

+--------------+--------+--------+--------+--------+--------------+
| student_name | 语文   | 数学   | 英语   | 总分   | 结果         |
+--------------+--------+--------+--------+--------+--------------+
| 林磊儿       | 优秀   | 优秀   | 优秀   |    445 | 重点大学     |
| 方一凡       | 差     | 差     | 差     |    419 | 一本         |
| 乔英子       | 普通   | 差     | 优秀   |    373 | 二本         |
| 陈哈哈       | 普通   | 普通   | 差     |    309 | 工地搬砖     |
+--------------+--------+--------+--------+--------+--------------+
4 rows in set (0.00 sec)

过来人的经验来看,老实孩子最吃亏,早知道他娘的走艺体了~

四、结束语

  好了,SQL方面就是以上这些内容了,有疑问可以写在评论区,哈哥会在摸鱼的时候回复你~~`

附录:创建表结构&测试数据SQL

表结构:

DROP TABLE IF EXISTS `t_gaokao_score`;
CREATE TABLE `t_gaokao_score`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生姓名',
  `subject` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '科目',
  `score` double NULL DEFAULT NULL COMMENT '成绩',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

导入测试数据:

INSERT INTO `t_gaokao_score` VALUES 
(1, '林磊儿', '语文', 148),
(2, '林磊儿', '数学', 150),
(3, '林磊儿', '英语', 147),
(4, '乔英子', '语文', 121),
(5, '乔英子', '数学', 106),
(6, '乔英子', '英语', 146),
(7, '方一凡', '语文', 70),
(8, '方一凡', '数学', 90),
(9, '方一凡', '英语', 59),
(10, '方一凡', '特长加分', 200),
(11, '陈哈哈', '语文', 109),
(12, '陈哈哈', '数学', 92),
(13, '陈哈哈', '英语', 80);

到此这篇关于MySQL 行转列详情的文章就介绍到这了,更多相关MySQL 行转列内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • MySQL 中行转列的方法

    MySQL行转列操作 所谓的行转列操作,就是将一个表的行信息转化为列信息,说着可能比较笼统,这里先举个例子,如下: +----+-----------+--------+-------+ | ID | USER_NAME | COURSE | SCORE | +----+-----------+--------+-------+ | | 张三 | 数学 | | | | 张三 | 语文 | | | | 张三 | 英语 | | | | 李四 | 数学 | | | | 李四 | 语文 | | | |

  • MySQL存储过程中使用动态行转列

    本文介绍的实例成功的实现了动态行转列.下面我以一个简单的数据库为例子,说明一下. 数据表结构 这里我用一个比较简单的例子来说明,也是行转列的经典例子,就是学生的成绩 三张表:学生表.课程表.成绩表 学生表 就简单一点,学生学号.学生姓名两个字段 CREATE TABLE `student` ( `stuid` VARCHAR(16) NOT NULL COMMENT '学号', `stunm` VARCHAR(20) NOT NULL COMMENT '学生姓名', PRIMARY KEY (`

  • mysql 行转列和列转行实例详解

    mysql行转列.列转行 语句不难,不做多余解释了,看语句时,从内往外一句一句剖析 行转列 有如图所示的表,现在希望查询的结果将行转成列 建表语句如下: CREATE TABLE `TEST_TB_GRADE` ( `ID` int(10) NOT NULL AUTO_INCREMENT, `USER_NAME` varchar(20) DEFAULT NULL, `COURSE` varchar(20) DEFAULT NULL, `SCORE` float DEFAULT '0', PRIM

  • MySQL中将一列以逗号分隔的值行转列的实现

    前言 有时会遇到没有遵守第一范式设计模式的业务表.即一列中存储了多个属性值.如下表 pk value 1 ET,AT 2 AT,BT 3 AT,DT 4 DT,CT,AT 一般有这两种常见需求(测试数据见文末) 1.得到所有的不重复的值,如 value AT BT CT DT ET SQL如下: select distinct(substring_index(substring_index(a.col,',',b.help_topic_id+1),',',-1)) from (select gr

  • MySQL 行转列详情

    目录 一.行转列SQL写法 二.如果领导@你,让你在结果集中加上总数列呢? 三.领导又双叒叕@你改需求 四.结束语 附录:创建表结构&测试数据SQL   MySQL行转列,对经常处理数据的同学们来说,一定是不陌生的,甚至是印象深刻,因为它大概率困扰过你,让你为之一愣~ 但当你看到本文后,这个问题就不在是问题,及时收藏,以后谁再问你这个问题,直接甩他脸上,粘贴即用. 首先,我们看一下咱们的测试表数据和预期查询的结果: mysql> SELECT * FROM t_gaokao_score; +

  • mysql 行转列与列传行

    目录 一.行转列—case+groupby 二.列转行——union 一.行转列— case+group by mysql> CREATE TABLE `TEST_TB_GRADE` (     ->  `ID` int(10) NOT NULL AUTO_INCREMENT,     ->  `USER_NAME` varchar(20) DEFAULT NULL,     ->  `COURSE` varchar(20) DEFAULT NULL,     ->  `SC

  • 学习mysql 如何行转列与列传行

    目录 一.行转列—case+groupby 二.列转行——union 一.行转列— case+group by mysql> CREATE TABLE `TEST_TB_GRADE` (     ->  `ID` int(10) NOT NULL AUTO_INCREMENT,     ->  `USER_NAME` varchar(20) DEFAULT NULL,     ->  `COURSE` varchar(20) DEFAULT NULL,     ->  `SC

  • MySQL表字段数量限制及行大小限制详情

    目录 字段数量限制 Column Count Limits 行大小限制 Row Size Limits 8126B限制 Row Limits总结 字段数量限制 Column Count Limits MySQL对于每个表的字段数量是严格控制在4096个,但是实际情况下的限制大小取决于以下因素(也就是基本达不到4096就被限制了): 表的行最大的row size会限制字段数量,如果当前row size过大就不能加字段了 The storage requirements of individual

  • MySQL中列转行和行转列总结解决思路

    目录 引言 列转行 行转列 总结 引言 在学习sql中遇到了列转行和行转列的题目,这里总结一下如何在对应的情景下解决不同的题目: 列转行 创建一个表stu_score_01: SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for stu_score_01 -- ---------------------------- DROP TABLE IF E

  • SQL行转列与列转行详情

    1.数据集 +---+----------+ |id |login_date| +---+----------+ |01 |2021-02-28| |01 |2021-03-01| |01 |2021-03-02| |01 |2021-03-04| |01 |2021-03-05| |01 |2021-03-06| |01 |2021-03-08| |02 |2021-03-01| |02 |2021-03-02| |02 |2021-03-03| |02 |2021-03-06| |03 |2

随机推荐