sql - 拆分空格分隔值并将它们映射到 SQLite 中的原始 ID？

Question

我有一张 table 叫 personal_websessions包含以下格式的数据:

 id_no | website_link 
 1     | google.com msn.com gmail.com 
 2     | stackoverflow.com reddit.com 
 3     | msn.com 

您可以使用以下 SQL 命令创建此表:

CREATE TABLE personal_websessions(id_no INTEGER PRIMARY KEY, website_link TEXT);
INSERT INTO personal_websessions VALUES(1, 'google.com msn.com gmail.com'), (2, 'stackoverflow.com reddit.com'), (3, 'msn.com ');

我要 将 website_link 列的值按空格“”拆分 获得下表结果:

id_no | website_link 
1     | google.com
1     | msn.com 
1     | gmail.com
2     | stackoverflow.com
2     | reddit.com
3     | msn.com

我想用 拆分 website_link 列单格为实现这一目标 - 我尝试了不同的方法，包括此处概述的方法:

But this example did not help that much as it was for comma separated not space separated

我知道有一种方法可以使用 sqlite 做到这一点，但我还没有弄清楚!任何帮助是极大的赞赏!

谢谢 - Goosfraba

Answer 1

用递归 CTE :

with recursive cte as (
  select id_no, trim(website_link) || ' ' website_link,
    substr(
      website_link, 
      1, 
      case 
        when website_link like '% %' then instr(website_link, ' ') - 1
        else website_link
      end
    ) link
  from personal_websessions
  union all                                                     
  select c.id_no, substr(c.website_link, length(c.link) + 2),
    substr(
      substr(c.website_link, length(c.link) + 2), 
      1, 
      instr(substr(c.website_link, length(c.link) + 2), ' ') - 1
    ) link
  from cte c                                               
  where substr(c.website_link, length(c.link) + 2) like '% %'
)
select id_no, link website_link
from cte
order by id_no

见 demo .
结果:

| id_no | website_link      |
| ----- | ----------------- |
| 1     | google.com        |
| 1     | msn.com           |
| 1     | gmail.com         |
| 2     | stackoverflow.com |
| 2     | reddit.com        |
| 3     | msn.com           |