Shulou(Shulou.com)06/01 Report--

CTE (Common Table Expressions) refers to common table expressions defined using the WITH statement.

For example:

testdb=# explain verbose WITH t1 AS ( SELECT * FROM t_w1 WHERE t_w1.id % 4 = 0 ) SELECT * FROM t1 JOIN t_w2 as t2 ON t2.id = t1.id; QUERY PLAN -------------------------------------------------------------------------- Hash Join (cost=167.74.. 359.00 rows=76 width=70) Output: t1.id, t1.c1, t2.id, t2.c1 Hash Cond: (t2.id = t1.id) CTE t1 -> Seq Scan on public.t_w1 (cost=0.00.. 166.50 rows=38 width=8) Output: t_w1.id, t_w1.c1 Filter: ((t_w1.id % 4) = 0) -> Seq Scan on public.t_w2 t2 (cost=0.00.. 153.00 rows=10000 width=8) Output: t2.id, t2.c1 -> Hash (cost=0.76.. 0.76 rows=38 width=62) Output: t1.id, t1.c1 -> CTE Scan on t1 (cost=0.00.. 0.76 rows=38 width=62) Output: t1.id, t1.c1(13 rows)

CTE allows you to:

1. Improve SQL readability: CTE allows you to "modularize"SQL statements and improve script readability, as shown in the example above

2. Implement recursion: Implement recursion by adding the RECURSIVE modifier to introduce itself

recursive

Recursion is often used to process data that logically has a hierarchical or tree structure.

For example:

drop table if exists t_cte;create table t_cte(id varchar(10),parent_id varchar(10));insert into t_cte values('1',NULL);insert into t_cte values('11','1');insert into t_cte values('12','1');insert into t_cte values('111','11');insert into t_cte values('112','11');insert into t_cte values('121','12');

id is the id of the data table,parent_id is the parent id of the id, through this field you can find the corresponding parent record, first request to print the tree structure of these data, the corresponding SQL statement is as follows:

WITH RECURSIVE ret AS( SELECT parent_id, id::text as name, id::text FROM t_cte WHERE id = '1' UNION ALL SELECT t.parent_id, t.parent_id || ' > ' || t.id as name, t.id FROM ret JOIN t_cte t ON t.parent_id = ret.id)SELECT parent_id, name FROM ret;

The WITH RECURSIVE statement contains two parts

Non-recurrent term (non-recurrent part)

In the above example:

SELECT parent_id, id::text as name, id::text FROM t_cte WHERE id = '1'

2. recursive term

In the above example:

SELECT t.parent_id, t.parent_id || ' > ' || t.id as name, t.id FROM ret JOIN t_cte t ON t.parent_id = ret.id

the procedure are as follows

1. Non-recurrent term. The result is a reference to ret in the recursive term, and this part of the result is placed in the worksheet

2. Repeat until the worksheet is empty: replace recursive self-references (ret in the example above) with the contents of the worksheet, execute recursive term, and replace the worksheet with the result

An empty worksheet is an end condition for CTE Recursive. For example,

SELECT t.parent_id, t.parent_id || ' > ' || t.id as name, t.id FROM ret JOIN t_cte t ON t.parent_id = ret.id

When return is null, recursion ends.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.