公司有一条SQL语句写的是:
WITH RECURSIVE r AS (
SELECT * FROM test1 as t WHERE m_id = 18
union ALL
SELECT t.* FROM trade.merchant as t, r WHERE tree.par = r.m_id
)
SELECT * FROM r where stat = 1 ORDER BY m_id实在是看不懂,上网找了找介绍WITH、和RECURSIVE的教程,都没讲的细的,就官方手册讲的全点,但我英文不好~
能大概介绍一下(最好有个小例子)这俩命令怎么用吗?另外它里面的t.*是什么意思?谢谢!
WITH RECURSIVE r AS (
SELECT * FROM test1 as t WHERE m_id = 18
union ALL
SELECT t.* FROM trade.merchant as t, r WHERE tree.par = r.m_id
)
SELECT * FROM r where stat = 1 ORDER BY m_id实在是看不懂,上网找了找介绍WITH、和RECURSIVE的教程,都没讲的细的,就官方手册讲的全点,但我英文不好~
能大概介绍一下(最好有个小例子)这俩命令怎么用吗?另外它里面的t.*是什么意思?谢谢!
ITH RECURSIVE。 使用RECURSIVE选项 参数后,可以 输出自己想要的。
例子1:1到100的和
WITH RECURSIVE t(n) AS (
VALUES (1)
union ALL
SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;
输出结果:5050
例子2:取得一棵树的所有孩子节点
WITH RECURSIVE r AS (
SELECT * FROM tree WHERE id = 1
union ALL
SELECT tree.* FROM tree, r WHERE tree.parent = r.id
)
SELECT * FROM r ORDER BY id;
id | parent
----+--------
1 |
2 | 1
3 | 1
4 | 3
SELECT * FROM test1 as t WHERE m_id = 18
union ALL
SELECT t.* FROM trade.merchant as t, r WHERE tree.par = r.m_id
)
SELECT * FROM r where stat = 1 ORDER BY m_id
它会一直递归调用,直到没有记录返回。第一步,会得到 SELECT * FROM test1 as t WHERE m_id = 18 ,一条记录。并放在R中
然后再执行 SELECT t.* FROM trade.merchant as t, r WHERE tree.par = r.m_id 放入R中。 之后不断循环这一步。
create table emp (empid integer,empparid integer,empname varchar(32));
insert into emp values (001,nul,'CEO');
insert into emp values (002,001,'CTO');
insert into emp values (003,001,'CFO');
insert into emp values (004,001,'OOO');
insert into emp values (005,002,'MANAGER'); mydb=> select * from emp;
empid | empparid | empname
-------+----------+---------
2 | 1 | CTO
3 | 1 | CFO
4 | 1 | OOO
5 | 2 | MANAGER
--查询empid=2下的所有记录
WITH RECURSIVE r AS (
SELECT * FROM emp WHERE empid = 2
union ALL
SELECT emp.* FROM emp, r WHERE emp.empparid = r.empid
)
SELECT * FROM r ORDER BY empid; empid | empparid | empname
-------+----------+---------
2 | 1 | CTO
5 | 2 | MANAGER
(2 rows)
这个示例能明白吧?
1、
SELECT * FROM emp WHERE empid = 2 执行后,存入1个临时表
2、
执行 SELECT emp.* FROM emp, r WHERE emp.empparid = r.empid ,
结果为
5 | 2 | MANAGER
3、继续执行 SELECT emp.* FROM emp, r WHERE emp.empparid = r.empid 直到
记录集为空(没有满足条件的记录);
4、将1、2、3的结果UNION ALL后输出。