PostgreSQL中WITH的用法

80951838

2019-09-05

with语句的用法

with查询在复杂查询中定义一个辅助语句（可以理解为在一个查询中定义的临时表），这一特性常用语复杂查询或递归查询应用场景

1.在复杂查询语句中

mydb@localhost:5432=>with t as
mydb-> (select generate_series(1, 3))
mydb-> select * from t;
 generate_series
-----------------
               1
               2
               3
(3 rows)

一开始定义了一条辅助语句取数，之后在主查询语句中查询t，定义的辅助语句就像是定义了一张临时表，对于复杂查询如果不使用CTE（通用表达式），可以通过创建视图方式简化SQL。

下面我们来看一个相对复杂的查询，CTE可以简化SQL并且减少嵌套，因为可以预先定义辅助语句，之后在主查询中多次调用。

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_utits,
       sum(amount) as product_sales
from orders
where region in (select region from top_regions)
group by region, product;

定义了regional_sales和top_regions两个辅助语句，regional_sales算出每个区域的总销售额，top_regions算出销售额在总销售额10%以上的所有区域，主查询通过辅助语句与orders表关联，算出顶级区域每件商品的销售额和销售量。

2.递归查询

with查询的一个重要属性是 RECURSIVE,使用RECURSIVE可以引用自己的输出，从而实现递归，一般用于层次结构和树状结构中。
先举一个简单的例子

mydb@localhost:5432=>with recursive t(x) as (
mydb(> select 1
mydb(> union
mydb(> select x + 1
mydb(> from t
mydb(> where x < 5
mydb(> )
mydb-> select sum(x) from t;
 sum
-----
  15
(1 row)

再举一个稍微复杂点的例子，

先创建一张表

mydb@localhost:5432=>create table test_area(id int4, name varchar(32), fatherid int4);
CREATE TABLE

向表里面写入测试数据

mydb@localhost:5432=>insert into test_area values
mydb-> (1, '中国', 0),
mydb-> (2, '辽宁', 1),
mydb-> (3, '山东', 1),
mydb-> (4, '沈阳', 2),
mydb-> (5, '大连', 2),
mydb-> (6, '济南', 3),
mydb-> (7, '和平区', 4),
mydb-> (8, '沈河区', 4);
INSERT 0 8

现在查看下表里面的数据

mydb@localhost:5432=>select * from test_area;
 id |  name  | fatherid
----+--------+----------
  1 | 中国   |        0
  2 | 辽宁   |        1
  3 | 山东   |        1
  4 | 沈阳   |        2
  5 | 大连   |        2
  6 | 济南   |        3
  7 | 和平区 |        4
  8 | 沈河区 |        4
(8 rows)

上面的数据明显具有层级关系

现在我们的需求是这个样子的，给定一个id，可以完整的打印出来完整的地名，比如id=7，需要得到中国辽宁沈阳和平区

直接展示代码

mydb@localhost:5432=>with recursive r as (
select * from test_area where id=7
union all
select test_area.* from test_area, r where test_area.id=r.fatherid
)
select * from r order by id;
 id |  name  | fatherid
----+--------+----------
  1 | 中国   |        0
  2 | 辽宁   |        1
  4 | 沈阳   |        2
  7 | 和平区 |        4
(4 rows)

接着我们使用string_agg来合并起来

mydb@localhost:5432=>with recursive r as (
select * from test_area where id=7
union all
select test_area.* from test_area, r where test_area.id=r.fatherid
)
select string_agg(name, '') from (select name from r order by id) as n;
     string_agg
--------------------
 中国辽宁沈阳和平区
(1 row)

入股我们修改下需求，例如给出id,我们需要得到id的地名和下面的所有子地名。以id=4为例

mydb@localhost:5432=>with recursive r as (
mydb(> select * from test_area where id=4
mydb(> union all
mydb(> select test_area.* from test_area, r where test_area.fatherid=r.id
mydb(> )
mydb-> select * from r order by id;
 id |  name  | fatherid
----+--------+----------
  4 | 沈阳   |        2
  7 | 和平区 |        4
  8 | 沈河区 |        4
(3 rows)