pg 的递归查询

2020-04-17   阅读(30)  

语法:

WITH RECURSIVE cte_name(
    CTE_query_definition -- non-recursive term
    UNION [ALL]
    CTE_query definion  -- recursive term
) SELECT * FROM cte_name;

 

例子:

行政区划表:xzqh   表里是全国行政区划的数据。

select * from xzqh order by dm;

 

递归查询南京市下面的所有地区

WITH RECURSIVE r AS (
	SELECT dm, mc, ls FROM xzqh where dm = '320100'
	union
	SELECT xzqh_.dm, xzqh_.mc, xzqh_.ls FROM xzqh xzqh_  INNER JOIN r on r.dm = xzqh_.ls)
SELECT dm, mc, ls FROM r

 

ConstXiong 备案号:苏ICP备16009629号-3