欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

mysql列轉行方法超詳細講解

 更新時間：2023年09月10日 10:06:00   作者：bankq  
mysql行列轉換在項目中應用的極其頻繁,下面這篇文章主要給大家介紹了關于mysql列轉行方法的相關資料,文中通過代碼介紹的非常詳細,需要的朋友可以參考下

一、列轉行

mysql 數(shù)據(jù)庫中，我們可能遇到將數(shù)據(jù)庫中某一列的數(shù)據(jù)（多個值，按照英文逗號分隔），轉化為多行數(shù)據(jù)（即一行轉多行），然后join關聯(lián)表，再轉化為一行數(shù)據(jù)

如：有兩張表，一用戶表，一張學科表，需要查詢學科表中的用戶姓名

用戶表

idusernameage
1zhangsan20
2lisi21
3wamhwu22

學科表

iduser_idssubject
11,2,3數(shù)學
22,3語文
31,2英語

我們首先需要把學科表中的user_ids拆分成多行

iduser_idsubject
11數(shù)學
12數(shù)學
13數(shù)學
22語文
23語文
31英語
32英語

二、普通的實現(xiàn)方式(需要依賴 mysql.help_topic 表)

SELECT
    a.id,
    a.subject,
    SUBSTRING_INDEX( SUBSTRING_INDEX( a.`user_ids`, ',', b.help_topic_id + 1 ), ',',-1 ) user_id
FROM
    test a
    JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( a.`user_ids`) - LENGTH( REPLACE ( a.`user_ids`, ',', '' ) ) + 1 );

三、mysql.help_topic 無權限處理辦法

mysql.help_topic 的作用是對 SUBSTRING_INDEX 函數(shù)出來的數(shù)據(jù)(也就是按照分割符分割出來的)數(shù)據(jù)連接起來做笛卡爾積。

如果 mysql.help_topic 沒有權限，可以自己創(chuàng)建一張臨時表，用來與要查詢的表連接查詢。

獲取該字段最多可以分割成為幾個字符串:

SELECT MAX(LENGTH(a.`user_ids`) - LENGTH(REPLACE(a.`user_ids`, ',', '' )) + 1) FROM `test` a;

創(chuàng)建臨時表，并給臨時表添加數(shù)據(jù):

注意:

  • 臨時表必須有一列從 0 或者 1 開始的自增數(shù)據(jù)
  • 臨時表表名隨意，字段可以只有一個
  • 臨時表示的數(shù)據(jù)量必須比 MAX(LENGTH(a.user_ids) - LENGTH(REPLACE(a.user_ids, ',', '' )) + 1) 的值大
DROP TABLE IF EXISTS `tmp_help_topic`;
CREATE TABLE IF NOT EXISTS `tmp_help_topic` (
  `help_topic_id` bigint(20) NOT NULL AUTO_INCREMENT ,
  PRIMARY KEY (`help_topic_id`)
);
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();

四、查詢函數(shù)

SELECT
    a.id,a.subject,SUBSTRING_INDEX(SUBSTRING_INDEX(a.`user_ids`, ',', b.help_topic_id), ',',-1 ) user_id
FROM
    test a
    JOIN tmp_help_topic b ON b.help_topic_id <= (LENGTH( a.`user_ids`) - LENGTH(REPLACE(a.`user_ids`, ',', '')) + 1 );

五、join用戶表，關聯(lián)用戶名

select 
t2.*,
u.username
from ( 
  SELECT
    a.id,a.subject,SUBSTRING_INDEX(SUBSTRING_INDEX(a.`user_ids`, ',', b.help_topic_id), ',',-1 ) user_id
FROM
    test a
    JOIN tmp_help_topic b ON b.help_topic_id <= (LENGTH( a.`user_ids`) - LENGTH(REPLACE(a.`user_ids`, ',', '')) + 1 ) 
) t2 join user u 
on u.id = t2.user_id
iduser_idsubjectusername
11數(shù)學zhangsan
12數(shù)學lisi
13數(shù)學wangwu
22語文lisi
23語文wangwu
31英語zhangsan
32英語lisi

六、將多行數(shù)據(jù)轉化為一行

select 
t2.*,
group_concat(u.username) username
from ( 
  SELECT
    a.id,a.subject,SUBSTRING_INDEX(SUBSTRING_INDEX(a.`user_ids`, ',', b.help_topic_id), ',',-1 ) user_id
FROM
    test a
    JOIN tmp_help_topic b ON b.help_topic_id <= (LENGTH( a.`user_ids`) - LENGTH(REPLACE(a.`user_ids`, ',', '')) + 1 ) 
) t2 join user u 
on u.id = t2.user_id
group by t2.id
idsubjectuser_idsusername
1數(shù)學1,2,3zhangsan,lisi,wangwu
2語文2,3lisi,wangwu
3英語1,2zhangsan,lisi

說明：

  • SUBSTRING_INDEX(SUBSTRING_INDEX(a.user_ids, ',', b.help_topic_id), ',',-1 ) 就是獲取 tmp_help_topic 表的 help_topic_id 字段的值作為 name 字段的第幾個子串
  • 使用了 join 就會把字段 user_ids 分為 (LENGTH( a.user_ids) - LENGTH(REPLACE(a.user_ids, ',', '')) + 1 ) 行，并且每行的字段剛好是 user_ids字段的第 help_topic_id 個子串

GROUP_CONCAT函數(shù)用于將GROUP BY產(chǎn)生的同一個分組中的值連接起來，返回一個字符串結果

GROUP_CONCAT函數(shù)首先根據(jù)GROUP BY指定的列進行分組，將同一組的列顯示出來，并且用分隔符分隔，由函數(shù)參數(shù)(字段名)決定要返回的列

語法結構

GROUP_CONCAT([DISTINCT] 要連接的字段 [ORDER BY 排序字段 ASC/DESC] [SEPARATOR '分隔符'])

說明:

(1) 使用DISTINCT可以排除重復值

(2) 如果需要對結果中的值進行排序，可以使用ORDER BY子句

(3) SEPARATOR '分隔符'是一個字符串值，默認為逗號

總結

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

相關文章

最新評論