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

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

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

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

一、行轉列

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

SELECT Name,
       CASE WHEN Subject='語文' THEN Result ELSE 0 END AS 語文,
       CASE WHEN Subject='數(shù)學' THEN Result ELSE 0 END AS 數(shù)學,
       CASE WHEN Subject='數(shù)學' THEN Result ELSE 0 END AS 物理
FROM cj;

第二步：將上面的結果看做一張表，從表中找出每一個新字段的最大值，對Name進行分組

SELECT T.Name,
       MAX(T.語文) 語文,
       MAX(T.數(shù)學) 數(shù)學,
       MAX(T.物理) 物理
FROM (SELECT Name,
       CASE WHEN Subject='語文' THEN Result ELSE 0 END AS 語文,
       CASE WHEN Subject='數(shù)學' THEN Result ELSE 0 END AS 數(shù)學,
       CASE WHEN Subject='數(shù)學' 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;

連表比子查詢要好

二、列轉行

建表

CREATE TABLE CJ2
(
    Name varchar(32),
    `語文` int(10),
    `數(shù)學` 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ù)學' cource,數(shù)學 result
FROM cj2
union all
SELECT Name,'物理' cource,物理 result
FROM cj2;
# 查詢后按照結果排序
SELECT *
FROM (SELECT Name,'語文' cource,語文 result
FROM cj2
union all
SELECT Name,'數(shù)學' cource,數(shù)學 result
FROM cj2
union all
SELECT Name,'物理' cource,物理 result
FROM cj2) T
ORDER BY T.Name;

三、多列轉一行

將科目與分數(shù)排在一列

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

四、一行轉多列

將上表還原

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

SELECT Name,
       CASE
           WHEN LOCATE('語文', 成績) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成績, '語文:', -1), ',', 1)
           else 0 end as 語文,
       CASE
           WHEN LOCATE('數(shù)學', 成績) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成績, '數(shù)學:', -1), ',', 1)
           else 0 end as 數(shù)學,
       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ù)學' Cource, T2.數(shù)學 result
FROM (SELECT Name,
             CASE
                 WHEN LOCATE('數(shù)學', 成績) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成績, '數(shù)學:', -1), ',', 1)
                 else 0 end as 數(shù)學
      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ù)學' Cource, T2.數(shù)學 result
FROM (SELECT Name,
             CASE
                 WHEN LOCATE('數(shù)學', 成績) > 0 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(成績, '數(shù)學:', -1), ',', 1)
                 else 0 end as 數(shù)學
      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;

五、行轉列的其他案例

準備一張result表

行轉列

# 查詢1000號學生四門科目的成績
select StudentNo,
       case when SubjectNo = 1 then StudentResult else 0 end as 高等數(shù)學1,
       case when SubjectNo = 2 then StudentResult else 0 end as 高等數(shù)學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ù)學1) math1, MAX(高等數(shù)學2) math2, MAX(java編程) java, MAX(hadoop理論) hadoop
from (select StudentNo,
             case when SubjectNo = 1 then StudentResult else 0 end as 高等數(shù)學1,
             case when SubjectNo = 2 then StudentResult else 0 end as 高等數(shù)學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;

到此這篇關于Mysql行與列的多種轉換(行轉列,列轉行,多列轉一行,一行轉多列)的文章就介紹到這了,更多相關Mysql行與列轉換內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

mysql的判斷語句方式
本文詳細介紹了SQL中的條件判斷（if...else...elseif...）、循環(huán)控制（while、repeat、loop）以及它們的使用方法和案例,涵蓋了SQL編程中的基本控制結構,對初學者和已有一定基礎的開發(fā)者都具有一定的參考價值
2025-12-12
javascript身份證驗證代碼
對于客戶端驗證用戶輸入的身份證是否符合格式的代碼，需要的朋友可以參考下。
2010-11-11
SQL中Lag()和LEAD()的用法示例詳解
LAG和LEAD函數(shù)為SQL開發(fā)者提供了強大的數(shù)據(jù)洞察力,尤其是在處理時間序列和趨勢分析問題時,下面這篇文章主要介紹了SQL中Lag()和LEAD()用法的相關資料,文中通過代碼介紹的非常詳細,需要的朋友可以參考下
2025-10-10
MySQL數(shù)據(jù)庫配置優(yōu)化的方案
我們總是希望MySQL能夠獲得更高的查詢性能，最好的辦法是弄清楚MySQL是如何優(yōu)化和執(zhí)行查詢的。本文講解MySQL在各個方面的優(yōu)化方向，方便后端開發(fā)人員在調優(yōu)和問題排查過程中找到切入點
2023-02-02
MySQL 用戶創(chuàng)建與授權最佳實踐
在MySQL中,用戶管理和權限控制是數(shù)據(jù)庫安全的重要組成部分,下面詳細介紹如何在MySQL中創(chuàng)建用戶并授予適當?shù)臋嘞?感興趣的朋友跟隨小編一起看看吧
2025-06-06
關于MyBatis連接MySql8.0版本的配置問題
這篇文章主要介紹了關于MyBatis連接MySql8.0版本的配置問題,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下
2020-12-12
MYSQL比對版本號字符串的方法實例
因為mysql的版本較多,所以最近也踩了一個因為版本號字符串的坑,下面這篇文章主要給大家介紹了關于MYSQL比對版本號字符串的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下
2022-05-05
MySQL 如何設計統(tǒng)計數(shù)據(jù)表
有些時候，改進數(shù)據(jù)表查詢性能的最佳方式是在同一張數(shù)據(jù)表中冗余一些繼承的數(shù)據(jù)。然而，有些時候需要新建完全獨立的統(tǒng)計或緩存數(shù)據(jù)表，尤其是在需要反復查詢的需求情況下。如果業(yè)務允許一些時間上的誤差的話，那么這種方式會更好。
2021-06-06
MYSQL的慢SQL優(yōu)化的實現(xiàn)
本文主要介紹了MYSQL的慢SQL優(yōu)化的實現(xiàn),從慢SQL定義、影響、常見場景與危害、識別與監(jiān)控、分析、優(yōu)化方法、執(zhí)行計劃分析、案例分析、總結與最佳實踐、常見優(yōu)化誤區(qū)、日常開發(fā)預防措施等方面詳細闡述,感興趣的可以了解一下
2026-05-05
Linux下安裝MySQL教程
上一篇文章詳細介紹windows下MySQL安裝教程，這篇就從最基本的安裝MySQL-Linux環(huán)境開始，文章為繞MySQL安裝展開內容,需要的朋友可以參考一下
2021-11-11