mysql實(shí)現(xiàn)列轉(zhuǎn)行和行轉(zhuǎn)列方式
更新時(shí)間：2025年08月05日 10:03:52 作者：北風(fēng)toto
這篇文章主要介紹了mysql實(shí)現(xiàn)列轉(zhuǎn)行和行轉(zhuǎn)列方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教
1、行轉(zhuǎn)列(將多行數(shù)據(jù)轉(zhuǎn)為單行多列)
1.1、使用 CASE WHEN + 聚合函數(shù)
SELECT
id,
MAX(CASE WHEN subject = '數(shù)學(xué)' THEN score ELSE NULL END) AS '數(shù)學(xué)',
MAX(CASE WHEN subject = '語(yǔ)文' THEN score ELSE NULL END) AS '語(yǔ)文',
MAX(CASE WHEN subject = '英語(yǔ)' THEN score ELSE NULL END) AS '英語(yǔ)'
FROM student_scores
GROUP BY id;
1.2、使用 IF + 聚合函數(shù)
SELECT
id,
MAX(IF(subject = '數(shù)學(xué)', score, NULL)) AS '數(shù)學(xué)',
MAX(IF(subject = '語(yǔ)文', score, NULL)) AS '語(yǔ)文',
MAX(IF(subject = '英語(yǔ)', score, NULL)) AS '英語(yǔ)'
FROM student_scores
GROUP BY id;
1.3、使用 PIVOT (MySQL 8.0+)
SELECT
id,
JSON_UNQUOTE(JSON_EXTRACT(pivot_data, '$.數(shù)學(xué)')) AS '數(shù)學(xué)',
JSON_UNQUOTE(JSON_EXTRACT(pivot_data, '$.語(yǔ)文')) AS '語(yǔ)文',
JSON_UNQUOTE(JSON_EXTRACT(pivot_data, '$.英語(yǔ)')) AS '英語(yǔ)'
FROM (
SELECT
id,
JSON_OBJECTAGG(subject, score) AS pivot_data
FROM student_scores
GROUP BY id
) AS t;
1.4、dataworks使用wm_concat函數(shù)和keyvalue
- 缺點(diǎn)：當(dāng)字符串存在英文冒號(hào)時(shí)會(huì)導(dǎo)致獲取的值為空；中文冒號(hào)不受影響
- 如果存在重復(fù)的數(shù)據(jù)，將導(dǎo)致取數(shù)時(shí)隨機(jī)取其中一個(gè)；核心原因?yàn)閣m_concat函數(shù)在拼接時(shí)順序不固定，哪怕是增加了order by也沒有用
- keyvalue從字符串中取值時(shí)，如果有重復(fù)key，從左到右取第一個(gè)key的值
select id
,keyvalue(column_value,'name') as name
,keyvalue(column_value,'age') as age
from (
select id
,wm_concat(';',concat(obj_name,':',obj_value)) as column_value
from school
group by id
)
;
-- 如果值存在英文冒號(hào)，導(dǎo)致取值為空的原因，看下面兩個(gè)sql例子即可理解
-- 返回null
select keyvalue('name:小紅:3737;age:13','name');
-- 返回3737
select keyvalue('name:小紅:3737;age:13','name:小紅');
2、列轉(zhuǎn)行(將多列數(shù)據(jù)轉(zhuǎn)為多行)
2.1、使用 UNION ALL
SELECT id, '數(shù)學(xué)' AS subject, 數(shù)學(xué) AS score FROM student_scores_pivot UNION ALL SELECT id, '語(yǔ)文' AS subject, 語(yǔ)文 AS score FROM student_scores_pivot UNION ALL SELECT id, '英語(yǔ)' AS subject, 英語(yǔ) AS score FROM student_scores_pivot ORDER BY id, subject;
2.2、使用 CROSS JOIN + 條件篩選
- 優(yōu)點(diǎn)是不用頻繁讀取磁盤
SELECT
s.id,
c.subject,
CASE c.subject
WHEN '數(shù)學(xué)' THEN s.數(shù)學(xué)
WHEN '語(yǔ)文' THEN s.語(yǔ)文
WHEN '英語(yǔ)' THEN s.英語(yǔ)
END AS score
FROM student_scores_pivot s
CROSS JOIN (
SELECT '數(shù)學(xué)' AS subject UNION ALL
SELECT '語(yǔ)文' UNION ALL
SELECT '英語(yǔ)'
) c;
- 同樣的語(yǔ)句，使用values和row
SELECT
s.id,
c.subject,
CASE c.subject
WHEN '數(shù)學(xué)' THEN s.數(shù)學(xué)
WHEN '語(yǔ)文' THEN s.語(yǔ)文
WHEN '英語(yǔ)' THEN s.英語(yǔ)
END AS score
FROM student_scores_pivot s
CROSS JOIN (
values
row('數(shù)學(xué)')
,row('語(yǔ)文')
,row('英語(yǔ)')
) c(subject);
2.3、使用 JSON 函數(shù) (MySQL 8.0+)
SELECT
id,
jt.subject,
jt.score
FROM student_scores_pivot,
JSON_TABLE(
JSON_OBJECT(
'數(shù)學(xué)', 數(shù)學(xué),
'語(yǔ)文', 語(yǔ)文,
'英語(yǔ)', 英語(yǔ)
),
'$.*' COLUMNS(
subject VARCHAR(10) PATH '$.key',
score INT PATH '$.value'
)
) AS jt;
3、動(dòng)態(tài)行轉(zhuǎn)列
- 對(duì)于不確定列名的情況，可以使用存儲(chǔ)過程動(dòng)態(tài)生成SQL：
DELIMITER //
CREATE PROCEDURE dynamic_pivot(IN table_name VARCHAR(100), IN row_id VARCHAR(100), IN pivot_col VARCHAR(100), IN value_col VARCHAR(100))
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE col_name VARCHAR(100);
DECLARE col_list TEXT DEFAULT '';
DECLARE cur CURSOR FOR
SELECT DISTINCT pivot_col FROM table_name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO col_name;
IF done THEN
LEAVE read_loop;
END IF;
SET col_list = CONCAT(col_list,
IF(col_list = '', '', ', '),
'MAX(CASE WHEN ', pivot_col, ' = ''', col_name, ''' THEN ', value_col, ' ELSE NULL END) AS `', col_name, '`');
END LOOP;
CLOSE cur;
SET @sql = CONCAT('SELECT ', row_id, ', ', col_list, ' FROM ', table_name, ' GROUP BY ', row_id, ';');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
-- 調(diào)用存儲(chǔ)過程
CALL dynamic_pivot('student_scores', 'id', 'subject', 'score');
4、詳細(xì)測(cè)試demo
4.1、dataworks使用wm_concat函數(shù)和keyvalue實(shí)現(xiàn)行轉(zhuǎn)列
-- 創(chuàng)建表
create table if not exists school (
`id` string,
`obj_name` string,
`obj_value` string
);
-- 插入測(cè)試數(shù)據(jù)
insert into school
values
('1','name','小明'),
('1','age','12'),
('2','name','小紅'),
('2','age','13')
;
-- 列轉(zhuǎn)行
select id
,keyvalue(column_value,'name') as name
,keyvalue(column_value,'age') as age
from (
select id
,wm_concat(';',concat(obj_name,':',obj_value)) as column_value
from school
group by id
)
;
總結(jié)
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
相關(guān)文章
Navicat for Mysql 字段注釋中文亂碼問題及解決
這篇文章主要介紹了Navicat for Mysql 字段注釋中文亂碼問題及解決方案,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-09-09
MySQL學(xué)習(xí)必備條件查詢數(shù)據(jù)
這篇文章主要介紹了MySQL學(xué)習(xí)必備條件查詢數(shù)據(jù)，首先通過利用where語(yǔ)句可以對(duì)數(shù)據(jù)進(jìn)行篩選展開主題相關(guān)內(nèi)容，具有一定的參考價(jià)值，需要的小伙伴可以參考一下，希望對(duì)你有所幫助2022-03-03
將數(shù)據(jù)插入到MySQL表中的詳細(xì)教程
這篇文章主要介紹了將數(shù)據(jù)插入到MySQL表中的詳細(xì)教程,文中給出了在PHP腳本中操作的示例,是MySQL入門學(xué)習(xí)中的基礎(chǔ)知識(shí),需要的朋友可以參考下2015-05-05
這篇文章主要介紹了Mac上安裝MySQL過程分享,本文共分4步完成,簡(jiǎn)單易懂,需要的朋友可以參考下2014-11-11
在高并發(fā)的場(chǎng)景下,大量的請(qǐng)求直接訪問Mysql很容易造成性能問題,下面這篇文章主要給大家介紹了關(guān)于MySQL如何保證備份數(shù)據(jù)一致性的相關(guān)資料,文中通過圖文介紹的非常詳細(xì),需要的朋友可以參考下2022-05-05
這篇文章主要介紹了Mysql5.7.14 linux版密碼忘記完美解決辦法,需要的朋友可以參考下2017-08-08
MySQL中DROP、DELETE與TRUNCATE的對(duì)比分析
在MySQL數(shù)據(jù)庫(kù)操作中,DROP、DELETE和TRUNCATE是三個(gè)常用的數(shù)據(jù)操作命令,本文將從多個(gè)維度對(duì)這三個(gè)命令進(jìn)行詳細(xì)對(duì)比和解析,幫助讀者更好地掌握它們的應(yīng)用,感興趣的朋友一起看看吧2025-07-07
Linux系統(tǒng)利用crontab定時(shí)備份Mysql數(shù)據(jù)庫(kù)方法
本文教你如果快速利用系統(tǒng)crontab來定時(shí)執(zhí)行備份文件，按日期對(duì)備份結(jié)果進(jìn)行保存2021-09-09
Windows?11?和?Rocky?9?Linux?平臺(tái)?MySQL?8.0.33?簡(jiǎn)易安裝詳細(xì)教程
這篇文章主要介紹了Windows?11和Rocky9?Linux平臺(tái)MySQL8.0.33簡(jiǎn)易安裝教程,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2023-05-05
在項(xiàng)目開發(fā)里面總是要查看后臺(tái)執(zhí)行的sql語(yǔ)句,mysql數(shù)據(jù)庫(kù)也不例外,下面這篇文章主要給大家介紹了關(guān)于MySQL如何查看正在運(yùn)行的SQL的相關(guān)資料,文中介紹的非常詳細(xì),需要的朋友可以參考下2023-01-01