目录
首先准备一张表
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
