目录一、准备工作[code]# 创建数据表 CREATE TABLE ChengJi ( Name varchar(32), Subject varchar(32), Result int(10) ); # 插入数据 insert into ChengJi values ('张三', '语文', 80), ('张三', '数学', 90), ('张三', '物理', 85), ('李四', '语文', 85), ('李四', '数学', 92), ('李四', '物理', 82);[/code]
二、行转列团体分两步走 1、先预处理数据，将数据进行开端的行转列，便于后续的分组处理 [code]select Name, case when Subject = '语文' then Result else 0 end as 'Chinese', case when Subject = '数学' then Result else 0 end as 'Math', case when Subject = '物理' then Result else 0 end as 'Pha' from ChengJi;[/code]
2、对预处理完毕的数据进行分组聚合，使多行数据汇聚到一个组内，到达数据会合的效果，这此中要注意的一点是：要明确按照哪个字段进行聚合操纵。 [code]with t1 as(select Name, case when Subject = '语文' then Result else 0 end as 'Chinese', case when Subject = '数学' then Result else 0 end as 'Math', case when Subject = '物理' then Result else 0 end as 'Pha' from ChengJi) select Name, sum(Chinese) as 'Chinese', sum(Math) as 'Math', sum(Pha) as 'Pha' from t1 group by Name;[/code]
三、列转行为便于明白，我们将刚才已经转置好的效果插入到一个效果表内 1、创建一个效果表 [code]create table ChengJi_2( Name varchar(255), Chinese int, Math int, Pha int );[/code]2、将行转列效果插入到效果表 [code]insert into chengji_2 with t1 as(select Name, case when Subject = '语文' then Result else 0 end as 'Chinese', case when Subject = '数学' then Result else 0 end as 'Math', case when Subject = '物理' then Result else 0 end as 'Pha' from ChengJi) select Name, sum(Chinese) as 'Chinese', sum(Math) as 'Math', sum(Pha) as 'Pha' from t1 group by Name ;[/code]
3、对效果表进行列转行的操纵，列转行相对于行转列较为简朴，可直接使用 union all 进行操纵。 [code]select Name,Chinese from ChengJi_2 union all select Name,Math from ChengJi_2 union all select Name,Pha from ChengJi_2;[/code]
四、特殊的列转行/行转列但是对于一些特殊的行列转置，以上方法就不再使用，通常情况下，我们的行列转置是有可以进行分组聚合操纵可以完成的，而生产实践中也多数如此，但是有时有一些特殊的操纵是以上方法无法完成的，这就需要一些特殊的行列转置来完成，对此，我给出了以下的方案。 1、准备工作，创建数据表并插入数据 [code]CREATE TABLE 2003a ( seat varchar(255) , status varchar(255) , rowid varchar(255) ) ; INSERT INTO 2003a VALUES ('2', '已预订', 'A'); INSERT INTO 2003a VALUES ('3', '未预订', 'A'); INSERT INTO 2003a VALUES ('4', '未预订', 'A'); INSERT INTO 2003a VALUES ('5', '未预订', 'A'); INSERT INTO 2003a VALUES ('6', '未预订', 'B'); INSERT INTO 2003a VALUES ('7', '未预订', 'B'); INSERT INTO 2003a VALUES ('8', '未预订', 'B'); INSERT INTO 2003a VALUES ('9', '未预订', 'B'); INSERT INTO 2003a VALUES ('10', '未预订', 'B'); INSERT INTO 2003a VALUES ('11', '未预订', 'C'); INSERT INTO 2003a VALUES ('12', '已预订', 'C'); INSERT INTO 2003a VALUES ('13', '已预订', 'C'); INSERT INTO 2003a VALUES ('14', '未预订', 'C'); INSERT INTO 2003a VALUES ('15', '未预订', 'C'); INSERT INTO 2003a VALUES ('16', '未预订', 'D'); INSERT INTO 2003a VALUES ('17', '未预订', 'D'); INSERT INTO 2003a VALUES ('18', '未预订', 'D'); INSERT INTO 2003a VALUES ('19', '未预订', 'D'); INSERT INTO 2003a VALUES ('20', '已预订', 'D');[/code]
2、明确需求 原有表的结构： [code]2,已预订,A 3,未预订,A[/code]需要完成的工作： [code]2,3 已预定，未预定 A,A[/code]在这里我们可以很显着的看出，我们需要做的就是怎样进行 行转列/列转行 的操纵，在这里的行列转置是整行/整列进行转置，不再是依靠某个字段进行分组处理或者使用 union all 进行团体操纵，因此，我是用以下方案来完成。 1、对原表字段进行 group_concat，指定 “，”为字段值之间的分隔符 [code]SELECT GROUP_CONCAT(rowid ORDER BY rowid ASC SEPARATOR ', ') AS rowid, GROUP_CONCAT(status ORDER BY rowid ASC SEPARATOR ', ') AS status, GROUP_CONCAT(seat ORDER BY rowid ASC SEPARATOR ', ') AS seat FROM ( SELECT rowid, status, seat from `2003a` ) AS subquery[/code]
2、将全部的字段按照值聚合到一个数据表格内之后，我们可以使用 union all 来进行字段拆分 [code]with t1 as (SELECT GROUP_CONCAT(rowid ORDER BY rowid ASC SEPARATOR ', ') AS rowid, GROUP_CONCAT(status ORDER BY rowid ASC SEPARATOR ', ') AS status, GROUP_CONCAT(seat ORDER BY rowid ASC SEPARATOR ', ') AS seat FROM ( SELECT rowid, status, seat from `2003a` ) AS subquery) select seat from t1 union all select status from t1 union all select rowid from t1[/code]
3、使用 SUBSTRING_INDEX来进行拆分，将全部的字段值拆分成单独的值 [code]with t1 as (SELECT GROUP_CONCAT(rowid ORDER BY rowid ASC SEPARATOR ', ') AS rowid, GROUP_CONCAT(status ORDER BY rowid ASC SEPARATOR ', ') AS status, GROUP_CONCAT(seat ORDER BY rowid ASC SEPARATOR ', ') AS seat FROM ( SELECT rowid, status, seat from `2003a` ) AS subquery) ,t2 as (select seat from t1 union all select status from t1 union all select rowid from t1) select SUBSTRING_INDEX(seat,',',1) as p1, SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',2),',',-1) as p2, SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',3),',',-1) as p3, SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',4),',',-1) as p4, SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',5),',',-1) as p5, SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',6),',',-1) as p6, SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',7),',',-1) as p7, SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',8),',',-1) as p8, SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',9),',',-1) as p9, SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',10),',',-1) as p10, SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',11),',',-1) as p11, SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',12),',',-1) as p12, SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',13),',',-1) as p13, SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',14),',',-1) as p14, SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',15),',',-1) as p15, SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',16),',',-1) as p16, SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',17),',',-1) as p17, SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',18),',',-1) as p18, SUBSTRING_INDEX(SUBSTRING_INDEX(seat,',',19),',',-1) as p19 from t2[/code]在这里需要注意的是：第一个SUBSTRING_INDEX我们取的是源数据的第一个值，第二个SUBSTRING_INDEX以及之后的，我们取得是源数据的倒数第一个值，因此这里需要注意一下我们给到的是“-1”
至此，我们使用group_concat()以及SUBSTRING_INDEX()来到达了特殊的行列转置操纵。 总结到此这篇关于SQL行列转置以及非常规的行列转置的文章就介绍到这了,更多相关SQL行列转置内容请搜索脚本之家从前的文章或继续欣赏下面的相关文章希望大家以后多多支持脚本之家！ 来源：https://www.jb51.net/database/326073221.htm 免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！ |
/6 
|手机版|小黑屋|梦想之都-俊月星空
( 粤ICP备18056059号 )|网站地图
GMT+8, 2025-7-1 22:31 , Processed in 0.041042 second(s), 19 queries .
Powered by Mxzdjyxk! X3.5
© 2001-2025 Discuz! Team.
