Mysql学习——行与列的多种转换
短信预约 -IT技能 免费直播动态提醒
目录
首先准备一张表
CREATE TABLE CJ( Name varchar(32), Subject varchar(32), Result int(10));# 插入数据insert into cjvalues ('张三', '语文', 80), ('张三', '数学', 90), ('张三', '物理', 85), ('李四', '语文', 85), ('李四', '数学', 92), ('李四', '物理', 82);
一、行转列
第一步，先将科目分类好：
SELECT Name, CASE WHEN Subject='语文' THEN Result ELSE 0 END AS 语文, CASE WHEN Subject='数学' THEN Result ELSE 0 END AS 数学, CASE WHEN Subject='数学' THEN Result ELSE 0 END AS 物理FROM cj;
第二步：将上面的结果看做一张表，从表中找出每一个新字段的最大值，对Name进行分组
SELECT T.Name, MAX(T.语文) 语文, MAX(T.数学) 数学, MAX(T.物理) 物理FROM (SELECT Name, CASE WHEN Subject='语文' THEN Result ELSE 0 END AS 语文, CASE WHEN Subject='数学' THEN Result ELSE 0 END AS 数学, CASE WHEN Subject='数学' THEN Result ELSE 0 END AS 物理FROM cj) TGROUP BY T.Name;
案例二：查询用户安装APP的情况
create table app( id int, app varchar(32));insert into app(id, app)VALUES (1, '微信'), (2, '快手'), (3, 'QQ'), (4, '抖音'), (5, '美团'), (6, '饿了么'), (7, '支付宝'), (8, '拼多多'), (9, '高德地图');CREATE TABLE app_install( uid int, app varchar(32));insert into app_install(uid, app)VALUES (1, '微信'), (1, '美团'), (2, '支付宝'), (2, '高德地图'), (3, '拼多多');select uid, case when app = '微信' then 1 else 0 end as 'wx', case when app = '快手' then 1 else 0 end as 'ks', case when app = 'QQ' then 1 else 0 end as 'qq', case when app = '抖音' then 1 else 0 end as 'dy', case when app = '美团' then 1 else 0 end as 'mt', case when app = '饿了么' then 1 else 0 end as 'elm', case when app = '支付宝' then 1 else 0 end as 'zfb', case when app = '拼多多' then 1 else 0 end as 'pdd', case when app = '高德地图' then 1 else 0 end as 'gd'from app_install;select t.uid, case when max(t.wx) then '已安装' else '未安装' end as 'wx', case when max(t.ks) then '已安装' else '未安装' end as 'ks', case when max(t.qq) then '已安装' else '未安装' end as 'qq', case when max(t.dy) then '已安装' else '未安装' end as 'dy', case when max(t.mt) then '已安装' else '未安装' end as 'mt', case when max(t.elm) then '已安装' else '未安装' end as 'eml', case when max(t.zfb) then '已安装' else '未安装' end as 'zfb', case when max(t.pdd) then '已安装' else '未安装' end as 'pdd', case when max(t.gd) then '已安装' else '未安装' end as 'gd'from (select uid, case when app = '微信' then 1 else 0 end as 'wx', case when app = '快手' then 1 else 0 end as 'ks', case when app = 'QQ' then 1 else 0 end as 'qq', case when app = '抖音' then 1 else 0 end as 'dy', case when app = '美团' then 1 else 0 end as 'mt', case when app = '饿了么' then 1 else 0 end as 'elm', case when app = '支付宝' then 1 else 0 end as 'zfb', case when app = '拼多多' then 1 else 0 end as 'pdd', case when app = '高德地图' then 1 else 0 end as 'gd'from app_install) tgroup by t.uid;连表比子查询要好
二、列转行
建表
CREATE TABLE CJ2( Name varchar(32), `语文` int(10), `数学` int(10), `物理` int(10));# 插入数据insert into cj2 values ('张三',80,90,90),('李四',85,92,92);原表：
SELECT Name,'语文' cource,语文 resultFROM cj2union allSELECT Name,'数学' cource,数学 resultFROM cj2union allSELECT Name,'物理' cource,物理 resultFROM cj2;# 查询后按照结果排序SELECT *FROM (SELECT Name,'语文' cource,语文 resultFROM cj2union allSELECT Name,'数学' cource,数学 resultFROM cj2union allSELECT Name,'物理' cource,物理 resultFROM cj2) TORDER BY T.Name;
三、多列转一行
将科目与分数排在一列
SELECT Name,GROUP_CONCAT(Subject,':',Result) 成绩FROM cjgroup by Name; 
四、一行转多列
将上表还原
# 建表CREATE TABLE CJ3( Name varchar(32), `成绩` varchar(50));# 插入数据insert into cj3values ('张三', '语文:80,数学:90,物理:85'), ('李四', '语文:85,数学:92,物理:82');
SELECT Name, CASE WHEN LOCATE('语文', 成绩) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成绩, '语文:', -1), ',', 1) else 0 end as 语文, CASE WHEN LOCATE('数学', 成绩) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成绩, '数学:', -1), ',', 1) else 0 end as 数学, CASE WHEN LOCATE('物理', 成绩) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成绩, '物理:', -1), ',', 1) else 0 end as 物理from cj3;
SELECT T1.Name, '语文' Cource, T1.语文 resultFROM (SELECT Name, CASE WHEN LOCATE('语文', 成绩) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成绩, '语文:', -1), ',', 1) else 0 end as 语文 from cj3) T1union allSELECT T2.Name, '数学' Cource, T2.数学 resultFROM (SELECT Name, CASE WHEN LOCATE('数学', 成绩) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成绩, '数学:', -1), ',', 1) else 0 end as 数学 from cj3) T2union allSELECT T3.Name, '物理' Cource, T3.物理 resultFROM (SELECT Name, CASE WHEN LOCATE('物理', 成绩) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成绩, '物理:', -1), ',', 1) else 0 end as 物理 from cj3) T3;
SELECT *FROM (SELECT T1.Name, '语文' Cource, T1.语文 resultFROM (SELECT Name, CASE WHEN LOCATE('语文', 成绩) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成绩, '语文:', -1), ',', 1) else 0 end as 语文 from cj3) T1union allSELECT T2.Name, '数学' Cource, T2.数学 resultFROM (SELECT Name, CASE WHEN LOCATE('数学', 成绩) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成绩, '数学:', -1), ',', 1) else 0 end as 数学 from cj3) T2union allSELECT T3.Name, '物理' Cource, T3.物理 resultFROM (SELECT Name, CASE WHEN LOCATE('物理', 成绩) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成绩, '物理:', -1), ',', 1) else 0 end as 物理 from cj3) T3) TORDER BY T.Name;
五、行转列的其他案例
准备一张result表
行转列
# 查询1000号学生四门科目的成绩select StudentNo, case when SubjectNo = 1 then StudentResult else 0 end as 高等数学1, case when SubjectNo = 2 then StudentResult else 0 end as 高等数学2, case when SubjectNo = 3 then StudentResult else 0 end as java编程, case when SubjectNo = 4 then StudentResult else 0 end as hadoop理论from resultwhere StudentNo = 1000;
# 简化select StudentNo, MAX(高等数学1) math1, MAX(高等数学2) math2, MAX(java编程) java, MAX(hadoop理论) hadoopfrom (select StudentNo, case when SubjectNo = 1 then StudentResult else 0 end as 高等数学1, case when SubjectNo = 2 then StudentResult else 0 end as 高等数学2, case when SubjectNo = 3 then StudentResult else 0 end as java编程, case when SubjectNo = 4 then StudentResult else 0 end as hadoop理论 from result where StudentNo = 1000) T;
来源地址：https://blog.csdn.net/Helen_1997_1997/article/details/128282029
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/27.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
