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

Question

我有一个包含 2 个字段的表:编号:文字建议:字符串(逗号分隔值)

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

例子:

注意:这个排名必须保证每次运行查询时都是一样的..

谢谢

Answer 1

如果您的数据库版本是 8.0+，则 with recursive cte as 子句可用于以下选择语句(在提供所需的 DML 之后，例如 create table 和 insert 语句):

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 |
+------+------------+--------+