mysql - 拆分字符串字段后添加行号

Question

我有一个包含 2 个字段的表： ID：文本 建议：字符串（逗号分隔值）

我想做一个选择查询，它会返回一个新的编号行，表示每个建议都有自己的编号，如原始字符串中所示

例子：

注意：每次运行查询时必须保证此排名相同。

谢谢

标签： mysqlsqldatabase

Answer 1

如果您的数据库版本为8.0+，则with recursive cte as子句可能会在以下选择语句中使用（在需要的 DML 提供后，例如创建表和插入语句）：

mysql> create table tab( ID int, suggestions varchar(25));
mysql> insert into tab values(1,'A,B,C');
mysql> insert into tab values(2,'D,E,F,G,H');
mysql> select q2.*,
              row_number() 
              over 
             (partition by q2.id order by q2.suggestion) as number 
  from 
(       
select distinct
       id, 
       substring_index(
        substring_index(suggestions, ',', q1.nr), 
        ',', 
        -1
       ) as suggestion
    from tab
    cross join
    (with recursive cte as
     (
      select 1 as nr
      union all
      select 1+nr from cte where nr<10
      )
     select * from cte) q1
) q2;

+------+------------+--------+
| id   | suggestion | number |
+------+------------+--------+
|    1 | A          |      1 |
|    1 | B          |      2 |
|    1 | C          |      3 |
|    2 | D          |      1 |
|    2 | E          |      2 |
|    2 | F          |      3 |
|    2 | G          |      4 |
|    2 | H          |      5 |
+------+------------+--------+