mysql實現(xiàn)列轉行和行轉列方式
更新時間：2025年08月05日 10:03:52 作者：北風toto
這篇文章主要介紹了mysql實現(xiàn)列轉行和行轉列方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教
1、行轉列(將多行數(shù)據(jù)轉為單行多列)
1.1、使用 CASE WHEN + 聚合函數(shù)
SELECT
id,
MAX(CASE WHEN subject = '數(shù)學' THEN score ELSE NULL END) AS '數(shù)學',
MAX(CASE WHEN subject = '語文' THEN score ELSE NULL END) AS '語文',
MAX(CASE WHEN subject = '英語' THEN score ELSE NULL END) AS '英語'
FROM student_scores
GROUP BY id;
1.2、使用 IF + 聚合函數(shù)
SELECT
id,
MAX(IF(subject = '數(shù)學', score, NULL)) AS '數(shù)學',
MAX(IF(subject = '語文', score, NULL)) AS '語文',
MAX(IF(subject = '英語', score, NULL)) AS '英語'
FROM student_scores
GROUP BY id;
1.3、使用 PIVOT (MySQL 8.0+)
SELECT
id,
JSON_UNQUOTE(JSON_EXTRACT(pivot_data, '$.數(shù)學')) AS '數(shù)學',
JSON_UNQUOTE(JSON_EXTRACT(pivot_data, '$.語文')) AS '語文',
JSON_UNQUOTE(JSON_EXTRACT(pivot_data, '$.英語')) AS '英語'
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
- 缺點：當字符串存在英文冒號時會導致獲取的值為空；中文冒號不受影響
- 如果存在重復的數(shù)據(jù)，將導致取數(shù)時隨機取其中一個；核心原因為wm_concat函數(shù)在拼接時順序不固定，哪怕是增加了order by也沒有用
- keyvalue從字符串中取值時，如果有重復key，從左到右取第一個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
)
;
-- 如果值存在英文冒號，導致取值為空的原因，看下面兩個sql例子即可理解
-- 返回null
select keyvalue('name:小紅:3737;age:13','name');
-- 返回3737
select keyvalue('name:小紅:3737;age:13','name:小紅');
2、列轉行(將多列數(shù)據(jù)轉為多行)
2.1、使用 UNION ALL
SELECT id, '數(shù)學' AS subject, 數(shù)學 AS score FROM student_scores_pivot UNION ALL SELECT id, '語文' AS subject, 語文 AS score FROM student_scores_pivot UNION ALL SELECT id, '英語' AS subject, 英語 AS score FROM student_scores_pivot ORDER BY id, subject;
2.2、使用 CROSS JOIN + 條件篩選
- 優(yōu)點是不用頻繁讀取磁盤
SELECT
s.id,
c.subject,
CASE c.subject
WHEN '數(shù)學' THEN s.數(shù)學
WHEN '語文' THEN s.語文
WHEN '英語' THEN s.英語
END AS score
FROM student_scores_pivot s
CROSS JOIN (
SELECT '數(shù)學' AS subject UNION ALL
SELECT '語文' UNION ALL
SELECT '英語'
) c;
- 同樣的語句，使用values和row
SELECT
s.id,
c.subject,
CASE c.subject
WHEN '數(shù)學' THEN s.數(shù)學
WHEN '語文' THEN s.語文
WHEN '英語' THEN s.英語
END AS score
FROM student_scores_pivot s
CROSS JOIN (
values
row('數(shù)學')
,row('語文')
,row('英語')
) c(subject);
2.3、使用 JSON 函數(shù) (MySQL 8.0+)
SELECT
id,
jt.subject,
jt.score
FROM student_scores_pivot,
JSON_TABLE(
JSON_OBJECT(
'數(shù)學', 數(shù)學,
'語文', 語文,
'英語', 英語
),
'$.*' COLUMNS(
subject VARCHAR(10) PATH '$.key',
score INT PATH '$.value'
)
) AS jt;
3、動態(tài)行轉列
- 對于不確定列名的情況，可以使用存儲過程動態(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 ;
-- 調用存儲過程
CALL dynamic_pivot('student_scores', 'id', 'subject', 'score');
4、詳細測試demo
4.1、dataworks使用wm_concat函數(shù)和keyvalue實現(xiàn)行轉列
-- 創(chuàng)建表
create table if not exists school (
`id` string,
`obj_name` string,
`obj_value` string
);
-- 插入測試數(shù)據(jù)
insert into school
values
('1','name','小明'),
('1','age','12'),
('2','name','小紅'),
('2','age','13')
;
-- 列轉行
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
)
;
總結
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關文章
mysql支持的字符串截取函數(shù)主要有?left()、right()、substring()、substring_index(),下面是這些函數(shù)的詳細使用方法2022-10-10
MySQL參數(shù)優(yōu)化信息參考(my.cnf參數(shù)優(yōu)化)
下面針對一些參數(shù)進行說明,當然還有其它的設置可以起作用,取決于你的負載或硬件：在慢內存和快磁盤、高并發(fā)和寫密集型負載情況下,你將需要特殊的調整2024-07-07
這篇文章主要介紹了MySQL連接異常報10061錯誤問題解決,本篇文章通過簡要的案例,講解了該項技術的了解與使用,以下就是詳細內容,需要的朋友可以參考下2021-08-08
這篇文章主要介紹了MySQL針對Discuz論壇程序的基本優(yōu)化教程,包括在緩存和索引等方面的優(yōu)化方法,需要的朋友可以參考下2015-11-11
安裝mysql出錯”A Windows service with the name MySQL already exis
這篇文章主要介紹了安裝mysql出錯”A Windows service with the name MySQL already exists.“如何解決的相關資料，在日常項目中此問題比較多見，特此把解決辦法分享給大家，供大家參考2016-05-05
在項目中我們會經(jīng)常遇到慢查詢，當我們遇到慢查詢的時候一般都要開啟慢查詢日志，本文主要介紹了MySQL如何快速定位慢SQL的實戰(zhàn)，文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-03-03
在數(shù)據(jù)庫日常維護中，最常做的事情就是SQL語句優(yōu)化，因為這個才是影響性能的最主要因素。這篇文章主要給大家介紹了關于MySQL優(yōu)化之如何寫出高質量sql語句的相關資料,需要的朋友可以參考下2021-05-05
MySQL數(shù)據(jù)庫事務隔離級別介紹(Transaction Isolation Level)
這篇文章主要介紹了MySQL數(shù)據(jù)庫事務隔離級別(Transaction Isolation Level) ,需要的朋友可以參考下2014-05-05
這篇文章主要給大家介紹了關于升級到MySQL5.7后開發(fā)不得不注意的一些坑，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2018-07-07
mysql重裝后出現(xiàn)亂碼解決辦法：只能在配置文件中將database 和 server 字符集 設置為utf8 ，否則不起作用，具體如下感興趣的朋友可以參考下哈，希望對大家有所幫助2013-07-07