本文共 2057 字,大约阅读时间需要 6 分钟。
oracle中的树状查询是比较常用的,通过connect by子句来实现。虽然在pg中默认不支持这种语法,但是可以用过with子句来实现类似的功能。
with子句的RECURSIVE选项将WITH从单纯的句法便利变成了一种在标准SQL中不能完成的特 性。通过使用RECURSIVE,一个WITH查询可以引用它自己的输出 。例如下面这张表tbl:
bill=# select * from tbl; id | pid ----+----- 1 | 1 2 | 1 3 | 1 4 | 2 5 | 3 6 | 4(6 rows)
我们想要查询id是1的这一行数据的子数据,在oracle中我们可以使用connect by子句,类似这样:
select * from tbl as t1start with t1.pid=1connect by t1.id = prior t1.pid;
但是在pg中该如何实现呢?这个时候可以使用with recursive语句递归的方法来实现:
bill=# with recursive tbl_result as ( bill(# select * from tbl where id=1 bill(# union all bill(# select t2.* from tbl t1 join tbl t2 on t1.id=t2.pid bill(# ) bill-# select * from tbl_result; id | pid ----+----- 1 | 1 1 | 1 2 | 1 3 | 1 4 | 2 5 | 3 6 | 4(7 rows)
一个递归WITH查询的通常形式总是一个非递归项,然后是UNION(或者UNION ALL),再然后是一个递归项,其中只有递归项能够包含对于查询自身输出的引用。
其大致流程如下:bill=# with recursive t(n) as(bill(# select 1bill(# union allbill(# select n+1 from t where n<100)bill-# select sum(n) from t; sum ------ 5050(1 row)
灵活使用with递归,有些场景下会取得意想不到的优化效果,比方说我们需要某稀疏列的唯一值,最容易想到的就是:
select distinct col from table ;
但是这种写法在数据量很大的时候效率很相当低:
bill=# create table sex (sex char(1), otherinfo text); CREATE TABLEbill=# create index idx_sex_1 on sex(sex); CREATE INDEXbill=# insert into sex select 'm', generate_series(1,5000000)||'this is test'; INSERT 0 5000000bill=# insert into sex select 'w', generate_series(1,5000000)||'this is test'; INSERT 0 5000000
普通的distinct写法(2535.802 ms):
bill=# select distinct sex from sex; sex ----- m w(2 rows)Time: 2535.802 ms (00:02.536)
with递归(0.995ms):
bill=# with recursive skip as ( bill(# ( bill(# select min(t.sex) as sex from sex t where t.sex is not null bill(# ) bill(# union all bill(# ( bill(# select (select min(t.sex) as sex from sex t where t.sex > s.sex and t.sex is not null) bill(# from skip s where s.sex is not null bill(# ) bill(# ) bill-# select * from skip where sex is not null; sex ----- m w(2 rows)Time: 0.995 ms
转载地址:http://nbcpz.baihongyu.com/