SQL行列轉(zhuǎn)置以及非常規(guī)的行列轉(zhuǎn)置示例代碼
一、準(zhǔ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ù)進(jìn)行初步的行轉(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、對(duì)預(yù)處理完畢的數(shù)據(jù)進(jìn)行分組聚合，使多行數(shù)據(jù)匯聚到一個(gè)組內(nèi)，達(dá)到數(shù)據(jù)集中的結(jié)果，這其中要注意的一點(diǎn)是：要明確按照哪個(gè)字段進(jìn)行聚合操作。
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é)果插入到一個(gè)結(jié)果表內(nèi)
1、創(chuàng)建一個(gè)結(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、對(duì)結(jié)果表進(jìn)行列轉(zhuǎn)行的操作，列轉(zhuǎn)行相對(duì)于行轉(zhuǎn)列較為簡單，可直接使用 union all 進(jìn)行操作。
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)列
但是對(duì)于一些特殊的行列轉(zhuǎn)置，以上方法就不再使用，通常情況下，我們的行列轉(zhuǎn)置是有可以進(jìn)行分組聚合操作可以完成的，而生產(chǎn)實(shí)踐中也多數(shù)如此，但是有時(shí)有一些特殊的操作是以上方法無法完成的，這就需要一些特殊的行列轉(zhuǎn)置來完成，對(duì)此，我給出了以下的方案。
1、準(zhǔn)備工作，創(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
在這里我們可以很明顯的看出，我們需要做的就是如何進(jìn)行 行轉(zhuǎn)列/列轉(zhuǎn)行 的操作，在這里的行列轉(zhuǎn)置是整行/整列進(jìn)行轉(zhuǎn)置，不再是依靠某個(gè)字段進(jìn)行分組處理或者使用 union all 進(jìn)行整體操作，因此，我是用以下方案來完成。
1、對(duì)原表字段進(jìn)行 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、將所有的字段按照值聚合到一個(gè)數(shù)據(jù)表格內(nèi)之后，我們可以使用 union all 來進(jìn)行字段拆分
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來進(jìn)行拆分，將所有的字段值拆分成單獨(dú)的值
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在這里需要注意的是：第一個(gè)SUBSTRING_INDEX我們?nèi)〉氖窃磾?shù)據(jù)的第一個(gè)值，第二個(gè)SUBSTRING_INDEX以及之后的，我們?nèi)〉檬窃磾?shù)據(jù)的倒數(shù)第一個(gè)值，因此這里需要注意一下我們給到的是“-1”
至此，我們使用group_concat()以及SUBSTRING_INDEX()來達(dá)到了特殊的行列轉(zhuǎn)置操作。
總結(jié)
到此這篇關(guān)于SQL行列轉(zhuǎn)置以及非常規(guī)的行列轉(zhuǎn)置的文章就介紹到這了,更多相關(guān)SQL行列轉(zhuǎn)置內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了MYSQL大量寫入問題優(yōu)化詳解，文中優(yōu)化點(diǎn)解釋的很清楚，讓人看完就明了，感興趣的同學(xué)可以閱讀理解下2021-03-03
這篇文章主要介紹了MySQL使用explain命令查看與分析索引的使用情況,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-12-12
MySQL 配置免密碼登錄的問題記錄(mysql_config_editor Configurati
這篇文章主要介紹了MySQL 配置免密碼登錄的問題記錄(mysql_config_editor Configuration),本文給大家介紹的非常詳細(xì),感興趣的朋友跟隨小編一起看看吧2024-08-08
一臺(tái)服務(wù)器部署兩個(gè)獨(dú)立的mysql數(shù)據(jù)庫操作實(shí)例
這篇文章主要給大家介紹了關(guān)于一臺(tái)服務(wù)器部署兩個(gè)獨(dú)立的mysql數(shù)據(jù)庫的相關(guān)資料,同一臺(tái)服務(wù)器裝兩個(gè)數(shù)據(jù)庫,可以通過虛擬化技術(shù)實(shí)現(xiàn),文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-03-03
Mysql啟動(dòng)中 InnoDB: Error: log file ./ib_logfile0 is of differe
對(duì)于使用了默認(rèn) my.cnf（一般教程都會(huì)教你使用support-files/my-medium.cnf）的Mysql服務(wù)來說如果中間使用了innodb的話，innodb默認(rèn)的log file大小是56M2011-05-05
解析MYSQL 數(shù)據(jù)庫導(dǎo)入SQL 文件出現(xiàn)亂碼的問題
本篇文章是對(duì)MYSQL數(shù)據(jù)庫導(dǎo)入SQL文件出現(xiàn)亂碼的問題進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
解決Can''t locate ExtUtils/MakeMaker.pm in @INC報(bào)錯(cuò)
今天小編就為大家分享一篇關(guān)于解決Can't locate ExtUtils/MakeMaker.pm in @INC報(bào)錯(cuò)，小編覺得內(nèi)容挺不錯(cuò)的，現(xiàn)在分享給大家，具有很好的參考價(jià)值，需要的朋友一起跟隨小編來看看吧2019-01-01
mysql字符串拼接并設(shè)置null值的實(shí)例方法
在本文中小編給大家整理的是關(guān)于mysql 字符串拼接+設(shè)置null值的實(shí)例內(nèi)容以及具體方法，需要的朋友們可以學(xué)習(xí)下。2019-09-09
使用Linux的Shell腳本定時(shí)處理MySQL超時(shí)
本文提供使用Linux的Shell腳本定時(shí)處理MySQL超時(shí)Locked進(jìn)程腳本2013-11-11
MySQL默認(rèn)值(DEFAULT)和非空約束(NOT NULL)的實(shí)現(xiàn)
本文主要介紹了MySQL默認(rèn)值(DEFAULT)和非空約束(NOT NULL)的實(shí)現(xiàn),文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2024-05-05