WITH RECURSIVE子句的替代项

Question

雪花数据库不支持递归with子句函数，需要帮助我如何实现下面的查询。下面的查询在Teradata中很好地工作

如果有人也能帮助我实现使用Python的目标，那就太好了

WITH RECURSIVE RECURTEMP(ID,KCODE,LVL)
AS(SELECT ID, MIN(KCODE) AS KCODE,1
FROM TABLE_A
GROUP BY 1
UNION ALL
SELECT b.ID, trim(a.KCODE)|| ';'||trim(b.KCODE), LVL+1
FROM TABLE_A a
INNER JOIN RECURTEMP b ON a.ID = b.ID AND a.KCODE > b.KCODE
)
SELECT * FROM RECURTEMP

啊！[结果]：https://imgur.com/a/ppSRXeT

^{pr2}$

输出如下

^{3}$

Answer 1

Snowflake现在支持递归WITH。在

你的疑问

WITH RECURSIVE RECURTEMP(ID,KCODE,LVL) AS(
SELECT 
  ID, 
  MIN(KCODE) AS KCODE,
  1
FROM 
  TABLE_A
GROUP BY
  1
UNION ALL
SELECT 
  b.ID, 
  trim(a.KCODE) || ';' || trim(b.KCODE)  AS KCODE, 
  LVL+1
FROM 
  TABLE_A a
  INNER JOIN RECURTEMP b ON (a.ID = b.ID AND a.KCODE > b.KCODE)
)
SELECT * FROM RECURTEMP

文章链接如下。在

https://docs.snowflake.net/manuals/user-guide/queries-cte.html#overview-of-recursive-cte-syntax