Mysql行與列的多種轉(zhuǎn)換(行轉(zhuǎn)列,列轉(zhuǎn)行,多列轉(zhuǎn)一行,一行轉(zhuǎn)多列)
更新時間：2023年08月24日 09:53:01 作者：雷神樂樂
在MySQL中,行轉(zhuǎn)列和列轉(zhuǎn)行都是非常有用的操作,本文就來介紹一下Mysql行與列的多種轉(zhuǎn)換,主要包括行轉(zhuǎn)列,列轉(zhuǎn)行,多列轉(zhuǎn)一行,一行轉(zhuǎn)多列,具有一定的參考價值,感興趣的可以了解一下
首先準(zhǔn)備一張表
CREATE TABLE CJ
(
Name varchar(32),
Subject varchar(32),
Result int(10)
);
# 插入數(shù)據(jù)
insert into cj
values ('張三', '語文', 80),
('張三', '數(shù)學(xué)', 90),
('張三', '物理', 85),
('李四', '語文', 85),
('李四', '數(shù)學(xué)', 92),
('李四', '物理', 82);
一、行轉(zhuǎn)列
第一步，先將科目分類好：
SELECT Name,
CASE WHEN Subject='語文' THEN Result ELSE 0 END AS 語文,
CASE WHEN Subject='數(shù)學(xué)' THEN Result ELSE 0 END AS 數(shù)學(xué),
CASE WHEN Subject='數(shù)學(xué)' THEN Result ELSE 0 END AS 物理
FROM cj;
第二步：將上面的結(jié)果看做一張表，從表中找出每一個新字段的最大值，對Name進行分組
SELECT T.Name,
MAX(T.語文) 語文,
MAX(T.數(shù)學(xué)) 數(shù)學(xué),
MAX(T.物理) 物理
FROM (SELECT Name,
CASE WHEN Subject='語文' THEN Result ELSE 0 END AS 語文,
CASE WHEN Subject='數(shù)學(xué)' THEN Result ELSE 0 END AS 數(shù)學(xué),
CASE WHEN Subject='數(shù)學(xué)' THEN Result ELSE 0 END AS 物理
FROM cj) T
GROUP 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) t
group by t.uid;連表比子查詢要好
二、列轉(zhuǎn)行
建表
CREATE TABLE CJ2
(
Name varchar(32),
`語文` int(10),
`數(shù)學(xué)` int(10),
`物理` int(10)
);
# 插入數(shù)據(jù)
insert into cj2 values ('張三',80,90,90),('李四',85,92,92);原表：
SELECT Name,'語文' cource,語文 result FROM cj2 union all SELECT Name,'數(shù)學(xué)' cource,數(shù)學(xué) result FROM cj2 union all SELECT Name,'物理' cource,物理 result FROM cj2; # 查詢后按照結(jié)果排序 SELECT * FROM (SELECT Name,'語文' cource,語文 result FROM cj2 union all SELECT Name,'數(shù)學(xué)' cource,數(shù)學(xué) result FROM cj2 union all SELECT Name,'物理' cource,物理 result FROM cj2) T ORDER BY T.Name;
三、多列轉(zhuǎn)一行
將科目與分數(shù)排在一列
SELECT Name,GROUP_CONCAT(Subject,':',Result) 成績 FROM cj group by Name;
四、一行轉(zhuǎn)多列
將上表還原
# 建表
CREATE TABLE CJ3
(
Name varchar(32),
`成績` varchar(50)
);
# 插入數(shù)據(jù)
insert into cj3
values ('張三', '語文:80,數(shù)學(xué):90,物理:85'),
('李四', '語文:85,數(shù)學(xué):92,物理:82');
SELECT Name,
CASE
WHEN LOCATE('語文', 成績) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成績, '語文:', -1), ',', 1)
else 0 end as 語文,
CASE
WHEN LOCATE('數(shù)學(xué)', 成績) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成績, '數(shù)學(xué):', -1), ',', 1)
else 0 end as 數(shù)學(xué),
CASE
WHEN LOCATE('物理', 成績) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成績, '物理:', -1), ',', 1)
else 0 end as 物理
from cj3;
SELECT T1.Name, '語文' Cource, T1.語文 result
FROM (SELECT Name,
CASE
WHEN LOCATE('語文', 成績) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成績, '語文:', -1), ',', 1)
else 0 end as 語文
from cj3) T1
union all
SELECT T2.Name, '數(shù)學(xué)' Cource, T2.數(shù)學(xué) result
FROM (SELECT Name,
CASE
WHEN LOCATE('數(shù)學(xué)', 成績) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成績, '數(shù)學(xué):', -1), ',', 1)
else 0 end as 數(shù)學(xué)
from cj3) T2
union all
SELECT T3.Name, '物理' Cource, T3.物理 result
FROM (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.語文 result
FROM (SELECT Name,
CASE
WHEN LOCATE('語文', 成績) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成績, '語文:', -1), ',', 1)
else 0 end as 語文
from cj3) T1
union all
SELECT T2.Name, '數(shù)學(xué)' Cource, T2.數(shù)學(xué) result
FROM (SELECT Name,
CASE
WHEN LOCATE('數(shù)學(xué)', 成績) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成績, '數(shù)學(xué):', -1), ',', 1)
else 0 end as 數(shù)學(xué)
from cj3) T2
union all
SELECT T3.Name, '物理' Cource, T3.物理 result
FROM (SELECT Name,
CASE
WHEN LOCATE('物理', 成績) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成績, '物理:', -1), ',', 1)
else 0 end as 物理
from cj3) T3) T
ORDER BY T.Name;
五、行轉(zhuǎn)列的其他案例
準(zhǔn)備一張result表
行轉(zhuǎn)列
# 查詢1000號學(xué)生四門科目的成績
select StudentNo,
case when SubjectNo = 1 then StudentResult else 0 end as 高等數(shù)學(xué)1,
case when SubjectNo = 2 then StudentResult else 0 end as 高等數(shù)學(xué)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;
# 簡化
select StudentNo, MAX(高等數(shù)學(xué)1) math1, MAX(高等數(shù)學(xué)2) math2, MAX(java編程) java, MAX(hadoop理論) hadoop
from (select StudentNo,
case when SubjectNo = 1 then StudentResult else 0 end as 高等數(shù)學(xué)1,
case when SubjectNo = 2 then StudentResult else 0 end as 高等數(shù)學(xué)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;
到此這篇關(guān)于Mysql行與列的多種轉(zhuǎn)換(行轉(zhuǎn)列,列轉(zhuǎn)行,多列轉(zhuǎn)一行,一行轉(zhuǎn)多列)的文章就介紹到這了,更多相關(guān)Mysql行與列轉(zhuǎn)換內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
mysql服務(wù)無法啟動報錯誤1067解決方法(mysql啟動錯誤1067 )
mysql服務(wù)無法啟動報錯誤1067解決方法，大家參考使用吧2013-12-12
本文中介紹了一個MySQL的存儲過程，其中涉及Cursor的使用2008-12-12
學(xué)習(xí)mysql之后的一點總結(jié)(基礎(chǔ))
學(xué)習(xí)mysql之后的一點總結(jié)，比較適合新手朋友2012-05-05
Flushtables和Flushtableswithreadlock是MySQL中的兩個命令,用于關(guān)閉表對象和清空查詢緩存,它們會等待所有正在運行的SQL請求執(zhí)行結(jié)束,如果請求操作的表集合為{tables},則會阻塞其他會話對{tables}表上的所有操作,包括查詢和寫操作2024-11-11
window10下mysql 8.0.20 安裝配置方法圖文教程
這篇文章主要為大家詳細介紹了window10下mysql 8.0.20 安裝配置方法圖文教程，文中示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2020-05-05
MySQL中配置文件my.cnf因權(quán)限問題導(dǎo)致無法啟動的解決方法
這篇文章主要給大家介紹了關(guān)于MySQL中配置文件my.cnf因權(quán)限問題導(dǎo)致無法啟動的解決方法，該無法啟動的錯誤提示代碼是：World-writable config file '/etc/my.cnf' is ignored，文中給出了詳細的解決方法，需要的朋友們下面來一起看看吧。2017-06-06
這篇文章主要介紹了淺談如何保證Mysql主從一致2022-03-03
Mysql到Elasticsearch高效實時同步Debezium實現(xiàn)
這篇文章主要為大家介紹了Mysql到Elasticsearch高效實時同步Debezium的實現(xiàn)方式，有需要的朋友可以借鑒參考下，希望能夠有所幫助2022-02-02
這篇文章主要為大家詳細介紹了java連不上MySQL8.0問題的解決方法，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-06-06
MySQL DBA 常用手冊小結(jié)，使用mysql的朋友可以參考下。2011-11-11