用于解析逗号分隔值的递归查询

虽然在单个列中存储多个值违反了规范化规则，但有时必须处理设计糟糕的遗留表。递归查询可以帮助将逗号分隔值转换为不同的行。

创建一个设计糟糕的表格并插入一些数据：

create table projects (name varchar(10), members varchar(1000));

insert into projects (name, members) values ('Luna', '1, 3, 4'), ('Terra', '2,3,5'); 

检查我们有什么：

select * from projects;

将输出

NAME       MEMBERS                                
---------- -------------------------
Luna       1, 3, 4                                                 
Terra      2,3,5           

2 record(s) selected.

使用公用表表达式（CTE）以递归方式将 MEMBERS 中每个逗号分隔的值提取到其自己的行中：

WITH parse (lvl, name, member, tail) AS (  
  SELECT 1, name,     
         CASE WHEN LOCATE(',',members) > 0 
              THEN TRIM(LEFT(members, LOCATE(',',members)-1))
              ELSE TRIM(members) 
         END,    
         CASE WHEN LOCATE(',',members) > 0 
              THEN SUBSTR(members, LOCATE(',',members)+1)    
              ELSE '' 
         END  
  FROM projects 
  UNION ALL  
  SELECT lvl + 1, name,      
         CASE WHEN LOCATE(',', tail) > 0 
              THEN TRIM(LEFT(tail, LOCATE(',', tail)-1))    
              ELSE TRIM(tail) 
         END,    
         CASE WHEN LOCATE(',', tail) > 0 
              THEN SUBSTR(tail, LOCATE(',', tail)+1)    
              ELSE '' 
         END
  FROM parse 
  WHERE lvl < 100 AND tail != '')
  SELECT name, integer(member) member FROM parse
  ORDER BY 1

将返回

NAME       MEMBER     
---------- -----------
Luna                 1
Luna                 3
Luna                 4
Terra                2
Terra                3
Terra                5

  6 record(s) selected. 

CTE 返回的结果可以用作常规表，例如通过将其连接到另一个表。例如，创建员工查找表：

create table employees (id integer, name varchar(20));
insert into employees (id, name) values (1, 'John'), (2, 'Peter'), 
                                        (3, 'Venkat'), (4, 'Mishka'), (5, 'Xiao');

然后是以下查询

WITH parse (lvl, name, member, tail) AS (  
  SELECT 1, name,     
         CASE WHEN LOCATE(',',members) > 0 
              THEN TRIM(LEFT(members, LOCATE(',',members)-1))
              ELSE TRIM(members) 
         END,    
         CASE WHEN LOCATE(',',members) > 0 
              THEN SUBSTR(members, LOCATE(',',members)+1)    
              ELSE '' 
         END  
  FROM projects 
  UNION ALL  
  SELECT lvl + 1, name,      
         CASE WHEN LOCATE(',', tail) > 0 
              THEN TRIM(LEFT(tail, LOCATE(',', tail)-1))    
              ELSE TRIM(tail) 
         END,    
         CASE WHEN LOCATE(',', tail) > 0 
              THEN SUBSTR(tail, LOCATE(',', tail)+1)    
              ELSE '' 
         END
  FROM parse 
  WHERE lvl < 100 AND tail != '')
  SELECT p.name "Project name", e.name "Member name" 
  FROM parse p
  INNER JOIN employees e
  ON e.id = integer(p.member)
  ORDER BY 1, 2

将返回

Project name Member name         
------------ --------------------
Luna         John                
Luna         Mishka              
Luna         Venkat              
Terra        Peter               
Terra        Venkat              
Terra        Xiao                

  6 record(s) selected.