Mysql行與列的多種轉(zhuǎn)換(行轉(zhuǎn)列,列轉(zhuǎn)行,多列轉(zhuǎn)一行,一行轉(zhuǎn)多列)

更新時(shí)間：2023年08月24日 09:53:01 作者：雷神樂樂

在MySQL中,行轉(zhuǎn)列和列轉(zhuǎn)行都是非常有用的操作,本文就來(lái)介紹一下Mysql行與列的多種轉(zhuǎn)換,主要包括行轉(zhuǎn)列,列轉(zhuǎn)行,多列轉(zhuǎn)一行,一行轉(zhuǎn)多列,具有一定的參考價(jià)值,感興趣的可以了解一下

CREATE TABLE CJ
(
    Name    varchar(32),
    Subject varchar(32),
    Result  int(10)
);
# 插入數(shù)據(jù)
insert into cj
values ('張三', '語(yǔ)文', 80),
       ('張三', '數(shù)學(xué)', 90),
       ('張三', '物理', 85),
       ('李四', '語(yǔ)文', 85),
       ('李四', '數(shù)學(xué)', 92),
       ('李四', '物理', 82);

一、行轉(zhuǎn)列

第一步，先將科目分類好：

SELECT Name,
       CASE WHEN Subject='語(yǔ)文' THEN Result ELSE 0 END AS 語(yǔ)文,
       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é)果看做一張表，從表中找出每一個(gè)新字段的最大值，對(duì)Name進(jìn)行分組

SELECT T.Name,
       MAX(T.語(yǔ)文) 語(yǔ)文,
       MAX(T.數(shù)學(xué)) 數(shù)學(xué),
       MAX(T.物理) 物理
FROM (SELECT Name,
       CASE WHEN Subject='語(yǔ)文' THEN Result ELSE 0 END AS 語(yǔ)文,
       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, '美團(tuán)'),
       (6, '餓了么'),
       (7, '支付寶'),
       (8, '拼多多'),
       (9, '高德地圖');
CREATE TABLE app_install
(
    uid int,
    app varchar(32)
);
insert into app_install(uid, app)
VALUES (1, '微信'),
       (1, '美團(tuán)'),
       (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 = '美團(tuán)' 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 = '美團(tuán)' 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),
    `語(yǔ)文` int(10),
    `數(shù)學(xué)` int(10),
    `物理` int(10)
);
# 插入數(shù)據(jù)
insert into cj2 values ('張三',80,90,90),('李四',85,92,92);

原表：

SELECT Name,'語(yǔ)文' cource,語(yǔ)文 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,'語(yǔ)文' cource,語(yǔ)文 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)一行

將科目與分?jǐn)?shù)排在一列

SELECT Name,GROUP_CONCAT(Subject,':',Result) 成績(jī)
FROM cj
group by Name;

四、一行轉(zhuǎn)多列

將上表還原

# 建表
CREATE TABLE CJ3
(
    Name varchar(32),
    `成績(jī)` varchar(50)
);
# 插入數(shù)據(jù)
insert into cj3
values ('張三', '語(yǔ)文:80,數(shù)學(xué):90,物理:85'),
       ('李四', '語(yǔ)文:85,數(shù)學(xué):92,物理:82');

SELECT Name,
       CASE
           WHEN LOCATE('語(yǔ)文', 成績(jī)) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成績(jī), '語(yǔ)文:', -1), ',', 1)
           else 0 end as 語(yǔ)文,
       CASE
           WHEN LOCATE('數(shù)學(xué)', 成績(jī)) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成績(jī), '數(shù)學(xué):', -1), ',', 1)
           else 0 end as 數(shù)學(xué),
       CASE
           WHEN LOCATE('物理', 成績(jī)) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成績(jī), '物理:', -1), ',', 1)
           else 0 end as 物理
from cj3;

SELECT T1.Name, '語(yǔ)文' Cource, T1.語(yǔ)文 result
FROM (SELECT Name,
             CASE
                 WHEN LOCATE('語(yǔ)文', 成績(jī)) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成績(jī), '語(yǔ)文:', -1), ',', 1)
                 else 0 end as 語(yǔ)文
      from cj3) T1
union all
SELECT T2.Name, '數(shù)學(xué)' Cource, T2.數(shù)學(xué) result
FROM (SELECT Name,
             CASE
                 WHEN LOCATE('數(shù)學(xué)', 成績(jī)) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成績(jī), '數(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('物理', 成績(jī)) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成績(jī), '物理:', -1), ',', 1)
                 else 0 end as 物理
      from cj3) T3;

SELECT *
FROM (SELECT T1.Name, '語(yǔ)文' Cource, T1.語(yǔ)文 result
FROM (SELECT Name,
             CASE
                 WHEN LOCATE('語(yǔ)文', 成績(jī)) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成績(jī), '語(yǔ)文:', -1), ',', 1)
                 else 0 end as 語(yǔ)文
      from cj3) T1
union all
SELECT T2.Name, '數(shù)學(xué)' Cource, T2.數(shù)學(xué) result
FROM (SELECT Name,
             CASE
                 WHEN LOCATE('數(shù)學(xué)', 成績(jī)) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成績(jī), '數(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('物理', 成績(jī)) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成績(jī), '物理:', -1), ',', 1)
                 else 0 end as 物理
      from cj3) T3) T
ORDER BY T.Name;

五、行轉(zhuǎn)列的其他案例

準(zhǔn)備一張result表

行轉(zhuǎn)列

# 查詢1000號(hào)學(xué)生四門科目的成績(jī)
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;

# 簡(jiǎn)化
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)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: