LightDB中的WITH查询

WITH提供了一种方式来书写在一个大型查询中使用的辅助语句。这些语句通常被称为公共表表达式或CTE，他们可以被看成是定义只在一个查询中存在的临时表。在WITH子句中的每一个辅助语句可以是一个SELECT、INSERT、UPDATE或DELETE，并且WITH子句本身也可以被附加到一个主语句，主语句也可以是SELECT、INSERT、UPDATE或DELETE。

我们将具体举一些例子进行说明，下面先造数据。

--建表
create table area(id int, name text, pid int);
-- 造数据
insert into area values(1, 'china', 0);
insert into area values(101, 'zhejiang', 1);
insert into area values(102, 'jiangsu', 1);
insert into area values(103, 'heilongjiang', 1);
insert into area values(10101, 'hangzhou', 101);
insert into area values(10102, 'ningbo', 101);
insert into area values(10103, 'shaoxing', 101);
insert into area values(10104, 'wenzhou', 101);
insert into area values(1010101, 'binjiang', 10101);
insert into area values(1010102, 'shangcheng', 10101);
insert into area values(1010103, 'xihu', 10101);
insert into area values(1010101, 'gongshu', 10101);
-- 切换到oracle模式数据库
postgres@oracledb=# select rownum,* from area;rownum |   id    |     name     |  pid  
--------+---------+--------------+-------1 |       1 | china        |     02 |     101 | zhejiang     |     13 |     102 | jiangsu      |     14 |     103 | heilongjiang |     15 |   10101 | hangzhou     |   1016 |   10102 | ningbo       |   1017 |   10103 | shaoxing     |   1018 |   10104 | wenzhou      |   1019 | 1010101 | binjiang     | 1010110 | 1010102 | shangcheng   | 1010111 | 1010103 | xihu         | 1010112 | 1010101 | gongshu      | 10101
(12 rows)

首先是WITH中的SELECT用法，WITH中SELECT的基本价值是将复杂的查询分解称为简单的部分。一个例子：

-- 
postgres@oracledb=# with ta as (select * from area where rownum < 10)
postgres@oracledb-# select * from ta;id    |     name     |  pid  
---------+--------------+-------1 | china        |     0101 | zhejiang     |     1102 | jiangsu      |     1103 | heilongjiang |     110101 | hangzhou     |   10110102 | ningbo       |   10110103 | shaoxing     |   10110104 | wenzhou      |   1011010101 | binjiang     | 10101
(9 rows)postgres@oracledb=# explain with ta as (select * from area where rownum < 10)
select * from ta;QUERY PLAN                               
-----------------------------------------------------------------------CTE Scan on ta  (cost=0.17..0.34 rows=9 width=40)CTE ta->  Count StopKey  (cost=0.00..0.17 rows=9 width=40)->  Seq Scan on area  (cost=0.00..22.00 rows=1200 width=40)
(4 rows)

还有一个用法是WITH递归，我们举个例子：

postgres@oracledb=# WITH RECURSIVE t(n) AS (
postgres@oracledb(#     VALUES (1)
postgres@oracledb(#   UNION ALL
postgres@oracledb(#     SELECT n+1 FROM t WHERE n < 100
postgres@oracledb(# )
postgres@oracledb-# SELECT sum(n) FROM t;sum  
------5050
(1 row)postgres@oracledb=# explain WITH RECURSIVE t(n) AS (VALUES (1)UNION ALLSELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;QUERY PLAN                                
-------------------------------------------------------------------------Aggregate  (cost=3.65..3.66 rows=1 width=8)CTE t->  Recursive Union  (cost=0.00..2.95 rows=31 width=4)->  Result  (cost=0.00..0.01 rows=1 width=4)->  WorkTable Scan on t t_1  (cost=0.00..0.23 rows=3 width=4)Filter: (n < 100)->  CTE Scan on t  (cost=0.00..0.62 rows=31 width=4)
(7 rows)

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！