MySQL：按分隔符拆分列並用另一列中的值替換數字

使用 MySQL，按分隔符拆分列並用另一列中的值替換數字。

我有一個名為“路徑”的列，其路徑類似於“/426/427/428”。我想用類別名稱替換類別編號。結果將類似於“/電腦/其他附件/雷射列印機”。

這個 mysql 查詢似乎是一個好的開始，但不知道如何從 ‘categories’ 表、‘path’ 列中獲取列值。

select distinct
 SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT TRIM(LEADING '/' FROM '/426/427/428')), '/', numbers.n), '/', -1) name
from
 (select @rownum := @rownum + 1 as n
       from categories
       cross join (select @rownum := 0) r
   ) numbers 
where n > 0
order by
  n

現在，此查詢將字元串正確拆分為：

426
427
428

接下來會是這樣的結果：

id    name
426   Computers
427   Other accessories
428   Laser printers

最後應該將“名稱”列合併到

'/Computers/Other accessories/Laser printers' 

細繩。

提前感謝您的幫助！

我收到以下查詢：

CREATE TEMPORARY TABLE IF NOT EXISTS 
t1
ENGINE=MyISAM
AS (
   SELECT
        n AS nr
        , SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT TRIM(LEADING '/' FROM '/426/427/428')), '/', tmp.n), '/', -1) AS catid
        , (
               SELECT name FROM category
               WHERE category.id = catid
           ) AS name
        , (
               SELECT path FROM category
               WHERE category.id = catid
           ) AS path
   FROM
     (SELECT @rownum := @rownum + 1 AS n, category.id, category.name, category.path
           FROM category
           CROSS JOIN (SELECT @rownum := 0) r
       ) AS tmp 
   GROUP BY catid
   ORDER BY
      n

);

SELECT * FROM t1;
DROP TEMPORARY TABLE t1;

結果是：

*nr*   *catid*   *name*   *path*  
1   426   Computers   /426  
2   427   Other accessories   /426/427  
3   428   Laser printers   /426/427/428  

這幾乎是好的，只需要將名稱列與分隔符合併即可。

所以最終的查詢是：

CREATE TEMPORARY TABLE IF NOT EXISTS 
t1
ENGINE=MyISAM
AS (
   SELECT
        n AS nr
        , SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT TRIM(LEADING '/' FROM '/426/427/428')), '/', tmp.n), '/', -1) AS catid
        , (
               SELECT name FROM category
               WHERE category.id = catid
           ) AS name
        , (
               SELECT path FROM category
               WHERE category.id = catid
           ) AS path
   FROM
     (SELECT @rownum := @rownum + 1 AS n, category.id, category.name, category.path
           FROM category
           CROSS JOIN (SELECT @rownum := 0) r
       ) AS tmp 
   GROUP BY catid
   ORDER BY
      n

);

/* SELECT * FROM t1; */
SELECT group_concat(name SEPARATOR '/') as path_long FROM t1;
DROP TEMPORARY TABLE t1;

我們得到如下查詢結果：

path_long
/Computers/Other accessories/Laser printers

引用自：https://dba.stackexchange.com/questions/202253