mysql 多行合并一行

多行合并一行

• GROUP_CONCAT()

SELECT GROUP_CONCAT(列名) FROM 表名 WHERE 主键  IN('','','')

QQ截图20230330143230.png

• 不想要 ， 就这样 REPLACE(group_concat(F_FullName),",","/")

REPLACE(group_concat(F_FullName),",","/")

QQ截图20230330144058.png

• IN 中的顺序是不对的，要相对就需要 用到 field

SELECT F_FullName FROM `base_organize` WHERE F_Id IN( '96240625-934F-490B-8AA6-0BC775B18468','9E998490-40B2-45EF-A82E-E225BF2109E1','407393336282841029') ORDER BY FIELD(F_Id,
'96240625-934F-490B-8AA6-0BC775B18468','9E998490-40B2-45EF-A82E-E225BF2109E1','407393336282841029'
);

QQ截图20230330145022.png

• 动态默认的查询结果不是我们想要的，我们想要（区，镇，村）

 SELECT REPLACE(GROUP_CONCAT(F_FullName),",","/")  FROM `base_organize` WHERE  EXISTS  (SELECT
    SUBSTRING_INDEX( SUBSTRING_INDEX( processed_data, ',', b.help_topic_id + 1 ), ',',- 1 ) AS F_DivisionName 
FROM
(
SELECT REPLACE
    (
        REPLACE ( REPLACE ( REPLACE (F_DivisionName, '[', '' ), ']', '' ), '"', '' ),
        ' ',
        '' 
    ) processed_data 
FROM
    `mz_person` WHERE F_Id = 121
) temp
JOIN mysql.help_topic  b ON b.help_topic_id < ( LENGTH( temp.processed_data ) - LENGTH( REPLACE ( temp.processed_data, ',', '' ) ) + 1 ) 
WHERE F_Id = SUBSTRING_INDEX( SUBSTRING_INDEX( processed_data, ',', b.help_topic_id + 1 ), ',',- 1 ) ) 

QQ截图20230330191916.png

• 修改

SELECT REPLACE(GROUP_CONCAT(F_FullName ORDER BY n.NOrder ),",","/") FROM `base_organize` m   RIGHT JOIN (SELECT
    SUBSTRING_INDEX( SUBSTRING_INDEX( processed_data, ',', b.help_topic_id + 1 ), ',',- 1 ) AS F_DivisionName ,b.help_topic_id AS NOrder
FROM
(
SELECT REPLACE
    (
        REPLACE ( REPLACE ( REPLACE (F_DivisionName, '[', '' ), ']', '' ), '"', '' ),
        ' ',
        '' 
    ) processed_data 
FROM
    `mz_person` WHERE F_Id = 121
) temp
JOIN mysql.help_topic  b ON b.help_topic_id < ( LENGTH( temp.processed_data ) - LENGTH( REPLACE ( temp.processed_data, ',', '' ) ) + 1 )) n ON  m.F_Id = n.F_DivisionName

QQ截图20230330192043.png

• 把得到的结果更新到指定字段

UPDATE `mz_person` a SET  a.F_ShowDivisionName = (SELECT REPLACE(GROUP_CONCAT(F_FullName ORDER BY n.NOrder ),",","/") FROM `base_organize` m   RIGHT JOIN (SELECT
    SUBSTRING_INDEX( SUBSTRING_INDEX( processed_data, ',', b.help_topic_id + 1 ), ',',- 1 ) AS F_DivisionName ,b.help_topic_id AS NOrder
FROM
(
SELECT REPLACE
    (
        REPLACE ( REPLACE ( REPLACE (F_DivisionName, '[', '' ), ']', '' ), '"', '' ),
        ' ',
        '' 
    ) processed_data 
FROM
    `mz_person` WHERE F_Id = 121
) temp
JOIN mysql.help_topic  b ON b.help_topic_id < ( LENGTH( temp.processed_data ) - LENGTH( REPLACE ( temp.processed_data, ',', '' ) ) + 1 )) n ON  m.F_Id = n.F_DivisionName)
  WHERE a.F_Id = 121;

• 存储过程

DELIMITER //
CREATE PROCEDURE updatePersonDivisionName(IN p INT)
BEGIN
  DECLARE total INT DEFAULT 0;
  WHILE p > 103 DO 
      SET total := p;
      SET p := p - 1;
      UPDATE `mz_person` a SET  a.F_ShowDivisionName = (SELECT REPLACE(GROUP_CONCAT(F_FullName ORDER BY n.NOrder ),",","/") FROM `base_organize` m   RIGHT JOIN (SELECT
    SUBSTRING_INDEX( SUBSTRING_INDEX( processed_data, ',', b.help_topic_id + 1 ), ',',- 1 ) AS F_DivisionName ,b.help_topic_id AS NOrder
FROM
(
SELECT REPLACE
    (
        REPLACE ( REPLACE ( REPLACE (F_DivisionName, '[', '' ), ']', '' ), '"', '' ),
        ' ',
        '' 
    ) processed_data 
FROM
    `mz_person` WHERE F_Id = total
) temp
JOIN mysql.help_topic  b ON b.help_topic_id < ( LENGTH( temp.processed_data ) - LENGTH( REPLACE ( temp.processed_data, ',', '' ) ) + 1 )) n ON  m.F_Id = n.F_DivisionName)
  WHERE a.F_Id = total;
END WHILE;
END //
DELIMITER;

• 调用存储过程

CALL updatePersonDivisionName(30050); 

总结

• 我们之前省市区是用 json 数据存在一个字段里的

  
  QQ截图20230330212643.png

• 每一个id 对应另一张表的主键，我们想动态拼接 以 （省/市/区）的形式放到一个冗余字段里，所以就需要先把数组转换成 列表形式

  
  QQ截图20230330212903.png

• 然后对应成中文名，顺序还要按省市区的顺序来（这里还不是，所以又花了时间想，参考上面）

  
  QQ截图20230330145022.png

• 然后利用存储过程直接更新到对应的字段里

  
  QQ截图20230330213128.png