WITH RECURSIVE递归（4个例子）

梦里梦外; 2024-03-27 15:07 12阅读 0赞

计算1到100的累加的结果。
    WITH RECURSIVE t(n) AS (
        VALUES (1)
      UNION ALL
        SELECT n+1 FROM t WHERE n < 100
    )
    SELECT sum(n) FROM t;

sum

5050

============================================================================

/*
    https://www.yiibai.com/html/postgresql/2013/080565.html
    create table company (
    	id int ,
    	name varchar (60),
    	age int ,
    	address varchar (30),
    	salary int
    );
    insert into company values(1,'Paul',32,'California',20000);
    insert into company values(2,'Allen',25, 'Texas',15000);
    insert into company values(3,'Teddy',23,'Norway',20000);
    insert into company values(4,'Mark',25,'Rich-Mond',65000);
    insert into company values(5,'David',27,'Texas',85000);
    insert into company values(6,'Kim',22,'South-Hall',45000);
    insert into company values(7,'James',24,'Houston',10000);
    */
     
    --递归WITH
    --找到小于20000的薪金总和
     
    WITH RECURSIVE t(n) AS (
    		VALUES (0)
    		UNION ALL
    		SELECT salary FROM company WHERE salary < 20000
    )
    SELECT SUM(n) FROM t
     
    --删除指定的公司行,RETURNING子句返回它们的内容;然后主查询读取输出，并将其插入到COMPANY1 表：
     
    CREATE TABLE COMPANY1(
       ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL
    );
    WITH moved_rows AS (
        DELETE FROM COMPANY
        WHERE
            SALARY >= 30000
        RETURNING *
    )
    INSERT INTO COMPANY1 (SELECT * FROM moved_rows);
     
    SELECT * FROM COMPANY;
    SELECT * FROM COMPANY1;

![70][]

![70 1][]

============================================================================![70 2][]

PS的HELP也没有讲得非常仔细，个人理解 :
    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后输出。

![70 3][]

============================================================================

定义下面这样的表，存储每个区域（省、市、区）的id，名字及上级区域的pid

create table tb(id varchar(3) , pid varchar(3) , name varchar(10)); 
     
    insert into tb values('002' , 0 , '浙江省'); 
    insert into tb values('001' , 0 , '广东省'); 
    insert into tb values('003' , '002' , '衢州市');  
    insert into tb values('004' , '002' , '杭州市') ; 
    insert into tb values('005' , '002' , '湖州市');  
    insert into tb values('006' , '002' , '嘉兴市') ; 
    insert into tb values('007' , '002' , '宁波市');  
    insert into tb values('008' , '002' , '绍兴市') ; 
    insert into tb values('009' , '002' , '台州市');  
    insert into tb values('010' , '002' , '温州市') ; 
    insert into tb values('011' , '002' , '丽水市');  
    insert into tb values('012' , '002' , '金华市') ; 
    insert into tb values('013' , '002' , '舟山市');  
    insert into tb values('014' , '004' , '上城区') ; 
    insert into tb values('015' , '004' , '下城区');  
    insert into tb values('016' , '004' , '拱墅区') ; 
    insert into tb values('017' , '004' , '余杭区') ; 
    insert into tb values('018' , '011' , '金东区') ; 
    insert into tb values('019' , '001' , '广州市') ; 
    insert into tb values('020' , '001' , '深圳市') ;

需要查出某个省，比如浙江省，管辖的所有市及市辖地区

with RECURSIVE cte as
    (
    select a.id,cast(a.name as varchar(100)) from tb a where id='002'
    union all 
    select k.id,cast(c.name||'>'||k.name as varchar(100)) as name  from tb k inner join cte c on c.id = k.pid
    )select id,name from cte ;

![70 4][]

![70 5][]

![70 6][]

![70 7][]

[70]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/5b4688b3dd774f1bb84add0fd867a844.png
[70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/77783976ffa5439ab2bec7ad044b9055.png
[70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/d5d646f85caa4539b0c3cea66099aedc.png
[70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/df619b388cbe4625a3843286e72163b2.png
[70 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/f7ca2388e92b4c09870df5c54e993f4b.png
[70 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/c69cb219237842f1a88f3acf1a1fcac8.png
[70 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/7f27dbec911643858ce969b1110841f7.png
[70 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/9b6043ba44f648b6a5cf8f6cead72ae1.png