sql with 语法 CTE 递归

参考：https://learnsql.com/blog/get-to-know-the-power-of-sql-recursive-queries/

An important point: CTEs may also have a recursive structure:

WITH RECURSIVE [cte_name] (column, ...) AS (
    [non-recursive_term]
    UNION ALL
    [recursive_term])
SELECT ... FROM [cte_name];

How does it work?

It's quite simple. In the first step a non-recursive term is evaluated. Next, for every result row of the previous evaluation, a recursive term is evaluated and its results are appended to the previous ones. The recursive term has access to results of the previously evaluated term.

• 第一步：执行 non-recursive 段的代码
• 第二步：对于上一步操作的每一行结果，后面的递归段代码会执行，并且它的结果会追加到前面的结果后面。 递归段的代码是可以接触到前面非递归段的代码执行的结果的。

下面是一段 WITH SQL代码，目的是获取 url 的前缀，并且用来统计数量。

WITH RECURSIVE
  url_3pos AS (
    select id, url, instr(url, '/') as pos, 1 as tm from image where instr(url, '/') > 0
    union all
    select id, url, instr(substr(url, pos + 1), '/') + pos as pos, tm + 1 as tm from url_3pos where tm < 4
  )
select substr(url, 1, pos) as prex, count(*) as cnt from url_3pos where tm = 3 and url like 'http%' group by prex order by cnt desc;

例如对于一个 url https://www.dytt8.net/html/gndy/jddy/20200526/60059.html，执行结果应该是 https://www.dytt8.net/

使用上面的 WITH SQL 执行 urls 表的结果如下：

prex|cnt
https://mmbiz.qpic.cn/|93814
https://pic1.zhimg.com/|50265
http://pic1.zhimg.com/|40532
http://pic2.zhimg.com/|40470
http://pic3.zhimg.com/|40317
http://pic4.zhimg.com/|39977
https://pic2.zhimg.com/|29817
http://mmbiz.qpic.cn/|25525
...