PostgreSQL高级特性-WITH查询

1、WITH查询

WITH查询是PostgreSQL的高级特性之一，这一特性通常被称为CTE（Common Table Expressions），WITH查询在复杂的查询语句中定义辅助语句（可以理解成查询语句中的临时表），这一特性常用于如下两种场景：

• 将多个子查询类似的写到通用WITH查询中
• 递归查询（树查询）

下面通过简单的例子分别对这两种场景的使用。

2、复杂查询

准备一张测试表及数据

create table t_order(
	id serial primary key,
	cus_name varchar(30),
	order_code varchar(30)
);

insert into t_order(cus_name,order_code) values
('小A','OC0001'),
('小B','OC0002'),
('小C','OC0003'),
('小D','OC0004');

先通过一个简单的例子看下效果：

with t as (
    select * from t_order
)
select * from t;
--执行结果
id|cus_name|order_code|
--+--------+----------+
 1|小A      |OC0001    |
 2|小B      |OC0002    |
 3|小C      |OC0003    |
 4|小D      |OC0004    |

通过上面的例子可以看出，CTE查询是先预定义，然后就可以像正常的表查询，那么可不可以定义多个呢，答案是可以的，如下：

with t as (
	select * from t_order where id = 1
),
b as (
	select * from t_order where id = 2
)
select * from t
union all 
select * from b;
---执行结果
id|cus_name|order_code|
--+--------+----------+
 1|小A      |OC0001    |
 2|小B      |OC0002    |

后定义的CTE查询可以使用先定义的，比如上面例子b可以使用t，反过来则不可以。

with t as (
	select * from t_order where id = 1
),
b as (
	select * from t_order where id = 2
	union all 
	select * from t
)
select * from b;
--执行结果
id|cus_name|order_code|
--+--------+----------+
 2|小B      |OC0002    |
 1|小A      |OC0001    |

3、递归查询

这种查询在实际应用场景，一般用于存在上下级关系的逻辑，先准备一个简单的地址库表。

create table md_area_info(
	area_code varchar(30) primary key ,
	area_name varchar(30),
	parent_code varchar(30)
)

insert into md_area_info (area_code,area_name,parent_code) values
('001','广东省','000'),
('002','广州市','001'),
('003','黄浦区','002'),
('004','天河区','002'),
('005','白云区','002'),
('006','花都区','002'),
('007','番禺区','002'),
('008','海珠区','002'),
('009','增城区','002'),
('010','从化区','002'),
('011','佛山市','001'),
('012','顺德区','011'),
('013','禅城区','011'),
('014','高明区','011'),
('015','东莞市','001'),
('016','清溪镇','015'),
('017','塘厦镇','015'),
('018','樟木头镇','015');

查询下级信息

with recursive mai as (
	select * from md_area_info m where m.area_code = '002'
	union all
	select m.* from md_area_info m 
	inner join mai i 
		on i.area_code = m.parent_code
)
select * from mai m

---执行结果
area_code|area_name|parent_code|
---------+---------+-----------+
002      |广州市      |001        |
003      |黄浦区      |002        |
004      |天河区      |002        |
005      |白云区      |002        |
006      |花都区      |002        |
007      |番禺区      |002        |
008      |海珠区      |002        |
009      |增城区      |002        |
010      |从化区      |002        |

查询上级信息

with recursive mai as (
	select * from md_area_info m where m.area_code = '003'
	union all
	select m.* from md_area_info m 
	inner join mai i 
		on i.parent_code = m.area_code
)
select * from mai m

---执行结果
area_code|area_name|parent_code|
---------+---------+-----------+
003      |黄浦区      |002        |
002      |广州市      |001        |
001      |广东省      |000        |

一般获取上级信息，在实际场景有可能需要拼接成一个字段显示，这里我们可以用string_agg函数来实现

with recursive mai as (
	select * from md_area_info m where m.area_code = '003'
	union all
	select m.* from md_area_info m
	inner join mai i 
		on i.parent_code = m.area_code
)
select string_agg(area_name, '/') from (select m.area_name from mai m order by m.area_code) t
--执行结果
string_agg |
-----------+
广东省/广州市/黄浦区|

从上面的例子也用到PostgreSQL的另外一个高级特性：批量插入