SQL行列轉(zhuǎn)置以及非常規(guī)的行列轉(zhuǎn)置示例代碼

更新時間：2024年08月17日 10:22:53 作者：yangjiwei0207

轉(zhuǎn)置即旋轉(zhuǎn)數(shù)據(jù)表的橫縱方向,常用來改變數(shù)據(jù)布局,以便用新的角度觀察,下面這篇文章主要給大家介紹了關(guān)于SQL行列轉(zhuǎn)置以及非常規(guī)行列轉(zhuǎn)置的相關(guān)資料,文中通過代碼介紹的非常詳細,需要的朋友可以參考下

一、準備工作

# 創(chuàng)建數(shù)據(jù)表
CREATE TABLE ChengJi
(
    Name    varchar(32),
    Subject varchar(32),
    Result  int(10)
);

# 插入數(shù)據(jù)
insert into ChengJi
values ('張三', '語文', 80),
       ('張三', '數(shù)學(xué)', 90),
       ('張三', '物理', 85),
       ('李四', '語文', 85),
       ('李四', '數(shù)學(xué)', 92),
       ('李四', '物理', 82);

二、行轉(zhuǎn)列

整體分兩步走

1、先預(yù)處理數(shù)據(jù)，將數(shù)據(jù)進行初步的行轉(zhuǎn)列，便于后續(xù)的分組處理

select Name,
       case
            when Subject = '語文' then Result else 0
       end as 'Chinese',
       case
           when Subject = '數(shù)學(xué)' then Result else 0
       end as 'Math',
       case
           when Subject = '物理' then Result else 0
       end as 'Pha'
from ChengJi;

2、對預(yù)處理完畢的數(shù)據(jù)進行分組聚合，使多行數(shù)據(jù)匯聚到一個組內(nèi)，達到數(shù)據(jù)集中的結(jié)果，這其中要注意的一點是：要明確按照哪個字段進行聚合操作。

with t1 as(select Name,
       case
            when Subject = '語文' then Result else 0
       end as 'Chinese',
       case
           when Subject = '數(shù)學(xué)' 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;

三、列轉(zhuǎn)行

為便于理解，我們將剛才已經(jīng)轉(zhuǎn)置好的結(jié)果插入到一個結(jié)果表內(nèi)

1、創(chuàng)建一個結(jié)果表

create table ChengJi_2(
    Name varchar(255),
    Chinese int,
    Math int,
    Pha int
);

2、將行轉(zhuǎn)列結(jié)果插入到結(jié)果表

insert into chengji_2
with t1 as(select Name,
       case
            when Subject = '語文' then Result else 0
       end as 'Chinese',
       case
           when Subject = '數(shù)學(xué)' 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
;

3、對結(jié)果表進行列轉(zhuǎn)行的操作，列轉(zhuǎn)行相對于行轉(zhuǎn)列較為簡單，可直接使用 union all 進行操作。

select Name,Chinese from ChengJi_2
union all
select Name,Math from ChengJi_2
union all
select Name,Pha from ChengJi_2;

四、特殊的列轉(zhuǎn)行/行轉(zhuǎn)列

但是對于一些特殊的行列轉(zhuǎn)置，以上方法就不再使用，通常情況下，我們的行列轉(zhuǎn)置是有可以進行分組聚合操作可以完成的，而生產(chǎn)實踐中也多數(shù)如此，但是有時有一些特殊的操作是以上方法無法完成的，這就需要一些特殊的行列轉(zhuǎn)置來完成，對此，我給出了以下的方案。

1、準備工作，創(chuàng)建數(shù)據(jù)表并插入數(shù)據(jù)

CREATE TABLE 2003a
(
    seat   varchar(255) ,
    status varchar(255) ,
    rowid  varchar(255)
)
;

INSERT INTO 2003a
VALUES ('2', '已預(yù)訂', 'A');
INSERT INTO 2003a
VALUES ('3', '未預(yù)訂', 'A');
INSERT INTO 2003a
VALUES ('4', '未預(yù)訂', 'A');
INSERT INTO 2003a
VALUES ('5', '未預(yù)訂', 'A');
INSERT INTO 2003a
VALUES ('6', '未預(yù)訂', 'B');
INSERT INTO 2003a
VALUES ('7', '未預(yù)訂', 'B');
INSERT INTO 2003a
VALUES ('8', '未預(yù)訂', 'B');
INSERT INTO 2003a
VALUES ('9', '未預(yù)訂', 'B');
INSERT INTO 2003a
VALUES ('10', '未預(yù)訂', 'B');
INSERT INTO 2003a
VALUES ('11', '未預(yù)訂', 'C');
INSERT INTO 2003a
VALUES ('12', '已預(yù)訂', 'C');
INSERT INTO 2003a
VALUES ('13', '已預(yù)訂', 'C');
INSERT INTO 2003a
VALUES ('14', '未預(yù)訂', 'C');
INSERT INTO 2003a
VALUES ('15', '未預(yù)訂', 'C');
INSERT INTO 2003a
VALUES ('16', '未預(yù)訂', 'D');
INSERT INTO 2003a
VALUES ('17', '未預(yù)訂', 'D');
INSERT INTO 2003a
VALUES ('18', '未預(yù)訂', 'D');
INSERT INTO 2003a
VALUES ('19', '未預(yù)訂', 'D');
INSERT INTO 2003a
VALUES ('20', '已預(yù)訂', 'D');

2、明確需求

原有表的結(jié)構(gòu)：

2,已預(yù)訂,A
3,未預(yù)訂,A

需要完成的工作：

2,3
已預(yù)定，未預(yù)定
A,A

在這里我們可以很明顯的看出，我們需要做的就是如何進行行轉(zhuǎn)列/列轉(zhuǎn)行的操作，在這里的行列轉(zhuǎn)置是整行/整列進行轉(zhuǎn)置，不再是依靠某個字段進行分組處理或者使用 union all 進行整體操作，因此，我是用以下方案來完成。

1、對原表字段進行 group_concat，指定 “，”為字段值之間的分隔符

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

2、將所有的字段按照值聚合到一個數(shù)據(jù)表格內(nèi)之后，我們可以使用 union all 來進行字段拆分

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

3、使用 SUBSTRING_INDEX來進行拆分，將所有的字段值拆分成單獨的值

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

在這里需要注意的是：第一個SUBSTRING_INDEX我們?nèi)〉氖窃磾?shù)據(jù)的第一個值，第二個SUBSTRING_INDEX以及之后的，我們?nèi)〉檬窃磾?shù)據(jù)的倒數(shù)第一個值，因此這里需要注意一下我們給到的是“-1”