用於解析逗號分隔值的遞迴查詢

雖然在單個列中儲存多個值違反了規範化規則，但有時必須處理設計糟糕的遺留表。遞迴查詢可以幫助將逗號分隔值轉換為不同的行。

建立一個設計糟糕的表格並插入一些資料：

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.