主页 / dba / 问题 / 202253
Accepted
klor
Asked: 2018-03-26 23:43:48 +0800 CST

MySQL：按分隔符拆分列并用另一列中的值替换数字

  • 772

使用 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'

细绳。

提前感谢您的帮助！

mysql string-splitting

1 个回答

  1. Best Answer

    我收到以下查询：

    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
    • 0

相关问题