1. 全栈程序员必看首页

5分钟搞懂MySQL – 行转列

全栈程序员-用户IM • • 未分类

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

大家好，又见面了，我是你们的朋友全栈君。

小伙伴想精准查找自己想看的MySQL文章？喏 → MySQL专栏目录 | 点击这里

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

  这里告诉大家一个小秘密，其实我和《小欢喜》里面的几位是同学还是好朋友~~今天我就拿我们哥儿几个高考成绩表来当测试表。嗯，兄弟们不信？我还有我们在学校里的合影呢，高三那年还没有PS，所以照片肯定是真的！??

在这里插入图片描述

废话不多说，首先，我们看一下咱们的测试表数据和预期查询的结果。

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进行 行转列
SELECT 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()进行 行转列：
SELECT 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);
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

发布者：全栈程序员-用户IM，转载请注明出处：https://javaforall.cn/132988.html原文链接：https://javaforall.cn

【正版授权，激活自己账号】： Jetbrains全家桶Ide使用，1年售后保障，每天仅需1毛

【官方授权 正版激活】： 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

赞 (0)
全栈程序员-用户IM全栈程序员-用户IM
0 0
blank

相关推荐

  • getattr getattribute_getparameter返回值

    getattr getattribute_getparameter返回值

    getattr getattribute_getparameter返回值问题描述今天开发验证码验证功能，需要将手机号和对应的验证码设置到session中以便后面的验证，具体代码如下：1.发送验证码并把验证码保存到session中protectedvoiddoPost(HttpServletRequestreq,HttpServletResponseresponse)throwsServletException,IOException{ try{mresponse=response;St

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年10月31日
  • 三角法激光雷达测距原理「建议收藏」

    三角法激光雷达测距原理「建议收藏」

    三角法激光雷达测距原理「建议收藏」现在对三角测距方法踩踩坑。买了一个rplidarA2,做工不错，挺漂亮的，更重要的是可以软件启动停止，噪声很小，而且反射检测灵敏度比较高（可以扫描到毛玻璃，　有些差的激光雷达检测不到毛玻璃上的反射）。使用之后感觉很不错。马上就有其他部门的同事过来看热闹，问这问那。于是本人热心讲解了一番。不过感觉他们还是那么萌萌的表情。在此，简单分享一下rplidarA2这种激光雷达的原理和重点技…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年6月2日
  • 离散均匀分布方差推导_离散型均匀分布的期望和方差

    离散均匀分布方差推导_离散型均匀分布的期望和方差

    离散均匀分布方差推导_离散型均匀分布的期望和方差【几何分布、离散均匀分布、连续均匀分布的特征函数及期望与方差】

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年9月15日
  • 算法刷题LeetCode中文版_leetcode100题

    算法刷题LeetCode中文版_leetcode100题

    算法刷题LeetCode中文版_leetcode100题算法题打卡：仅仅反转字母。没有特别幸运，那么请先特别努力，别因为懒惰而失败，还矫情地将原因归于自己倒霉。所以说，树倒了，没有一片雪花是无辜的

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年8月31日
  • JAVA String 截取字符串的方法（含 substring 索引截取示例）

    JAVA String 截取字符串的方法（含 substring 索引截取示例）

    String.substring()：用于返回一个字符串的子串用法如下：string.substring(from,to)其中from指代要抽去的子串第一个字符在原字符串中的位置to指代所要抽去的子字符串最后一个字符的后一位（这个参数可以不加）下面就对String.substring()做举例：1、string.substring(from)：此时相当于从from位置截取到原字…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年4月3日
  • 求原根_模12的原根

    求原根_模12的原根

    求原根_模12的原根今天学了数论。。。求原根真的好暴力设模数为p我们把p−1p−1p-1分解质因数，对于每一个2≤i≤p−12≤i≤p−12\leqi\leqp-1,判断an−1pi%pan−1pi%pa^{n-1\overp_i}\%p是否为1，如果是，那么这个数就不是原根，否则就是ACCode#include<cstdio>#include<iostre

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年10月27日

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号