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也沒(méi)有用
- 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ǔ)過(guò)程動(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ǔ)過(guò)程
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)文章
mysql支持的字符串截取函數(shù)主要有?left()、right()、substring()、substring_index(),下面是這些函數(shù)的詳細(xì)使用方法2022-10-10
MySQL參數(shù)優(yōu)化信息參考(my.cnf參數(shù)優(yōu)化)
下面針對(duì)一些參數(shù)進(jìn)行說(shuō)明,當(dāng)然還有其它的設(shè)置可以起作用,取決于你的負(fù)載或硬件：在慢內(nèi)存和快磁盤、高并發(fā)和寫密集型負(fù)載情況下,你將需要特殊的調(diào)整2024-07-07
MySQL連接異常報(bào)10061錯(cuò)誤問(wèn)題解決
這篇文章主要介紹了MySQL連接異常報(bào)10061錯(cuò)誤問(wèn)題解決,本篇文章通過(guò)簡(jiǎn)要的案例,講解了該項(xiàng)技術(shù)的了解與使用,以下就是詳細(xì)內(nèi)容,需要的朋友可以參考下2021-08-08
MySQL針對(duì)Discuz論壇程序的基本優(yōu)化教程
這篇文章主要介紹了MySQL針對(duì)Discuz論壇程序的基本優(yōu)化教程,包括在緩存和索引等方面的優(yōu)化方法,需要的朋友可以參考下2015-11-11
安裝mysql出錯(cuò)”A Windows service with the name MySQL already exis
這篇文章主要介紹了安裝mysql出錯(cuò)”A Windows service with the name MySQL already exists.“如何解決的相關(guān)資料，在日常項(xiàng)目中此問(wèn)題比較多見(jiàn)，特此把解決辦法分享給大家，供大家參考2016-05-05
在項(xiàng)目中我們會(huì)經(jīng)常遇到慢查詢，當(dāng)我們遇到慢查詢的時(shí)候一般都要開啟慢查詢?nèi)罩?，本文主要介紹了MySQL如何快速定位慢SQL的實(shí)戰(zhàn)，文中通過(guò)示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2022-03-03
MySQL優(yōu)化之如何寫出高質(zhì)量sql語(yǔ)句
在數(shù)據(jù)庫(kù)日常維護(hù)中，最常做的事情就是SQL語(yǔ)句優(yōu)化，因?yàn)檫@個(gè)才是影響性能的最主要因素。這篇文章主要給大家介紹了關(guān)于MySQL優(yōu)化之如何寫出高質(zhì)量sql語(yǔ)句的相關(guān)資料,需要的朋友可以參考下2021-05-05
MySQL數(shù)據(jù)庫(kù)事務(wù)隔離級(jí)別介紹(Transaction Isolation Level)
這篇文章主要介紹了MySQL數(shù)據(jù)庫(kù)事務(wù)隔離級(jí)別(Transaction Isolation Level) ,需要的朋友可以參考下2014-05-05
升級(jí)到MySQL5.7后開發(fā)不得不注意的一些坑
這篇文章主要給大家介紹了關(guān)于升級(jí)到MySQL5.7后開發(fā)不得不注意的一些坑，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2018-07-07
mysql重裝后出現(xiàn)亂碼設(shè)置為utf8可解決
mysql重裝后出現(xiàn)亂碼解決辦法：只能在配置文件中將database 和 server 字符集 設(shè)置為utf8 ，否則不起作用，具體如下感興趣的朋友可以參考下哈，希望對(duì)大家有所幫助2013-07-07