postgresql with 递归查询

最新推荐文章于 2024-04-20 21:42:59 发布

huiyang23

最新推荐文章于 2024-04-20 21:42:59 发布

阅读量480

点赞数

分类专栏： greenplum 文章标签： postgresql tree sql oracle 数据库

greenplum 专栏收录该内容

7 篇文章

订阅专栏

本文介绍 PostgreSQL 8.4 中新增的 WITH 和 WITH RECURSIVE 子查询功能，演示如何利用这些功能进行递归查询，包括求1到100的和及获取树形结构所有子节点的例子。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

http://blog.youkuaiyun.com/wh62592855/article/details/6443909

Oracle数据库中的用 CONNECT BY来做递归查询。
PostgreSQL8.3以前是用connectby()函数来做递归查询。 connectby() 函数是 contrib/tablefunc模块下的功能，默认是没有安装的，需要自己安装。
PostgreSQL8.4中当然可以用connectby()函数来做递归查询，且系统默认安装了这个功能。下面简单介绍一下 PostgreSQL8.4增加的新功能递归 SQL 查询的用法。
1. WITH关键字。 WITH是属于子查询范畴
使用例子：
WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ), top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;
这个SQL是显示这个地区（region），销售额top的商品的销售信息。

2. WITH RECURSIVE。使用RECURSIVE选项参数后，可以输出自己想要的。
例子1：1到100的和
WITH RECURSIVE t(n) AS (
    VALUES (1)
union ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;
输出结果：5050

例子2：取得一棵树的所有孩子节点
WITH RECURSIVE r AS (
       SELECT * FROM tree WHERE id = 1
     union ALL
       SELECT tree.* FROM tree, r WHERE tree.parent = r.id
     )
   SELECT * FROM r ORDER BY id;
id | parent
----+--------
1 |
2 |      1
3 |      1
4 |      3

具体的用法可以参考PostgreSQL的用户手册。（ http://www.postgresql.org/docs/8.4/static/queries-with.html ）