Mysql之行與列的多種轉(zhuǎn)換實現(xiàn)方式

更新時間：2026年03月25日 10:00:35 作者：雷神樂樂

這篇文章主要介紹了Mysql之行與列的多種轉(zhuǎn)換實現(xiàn)方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

Mysql行與列的多種轉(zhuǎ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='物理' 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='物理' 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)列的其他案例

準備一張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;