累加求和运算
-- 计算递归 WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100 ) SELECT sum(n) FROM t;
递归拆分数据-分表
-- 测试工资递归 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); --找到小于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;
省市县区域字符串拼接
-- 省市区域字符串连接拼接测试 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 ;
MYSQL和POSTGRESQL 都支持WITH RECURSIVE语法:
https://www.cnblogs.com/kungfupanda/p/5628769.html
应用示例
查看组织机构递归
-- 同表递归查询 with RECURSIVE orgIdRelations as ( select a.i_orgid,cast(a.i_orgid as varchar(100)) as parentIds from xh_ht.fs_yw_base_org a where a.i_orgid =1 union all select k.i_orgid, cast(c.parentIds||','||k.i_orgid as varchar(100)) as parentIds from xh_ht.fs_yw_base_org k inner join orgIdRelations c on c.i_orgid = k.i_orgpid ) select i_orgid,parentIds from orgIdRelations ;
查看组织机构名称递归
-- 同表递归查询 with RECURSIVE orgNames as ( select a.i_orgid,cast(a.c_orgname as varchar(1000)) as orgName from xh_ht.fs_yw_base_org a where a.i_orgid=1 union all select k.i_orgid,cast(c.orgName||'>'||k.c_orgname as varchar(1000)) as orgName from xh_ht.fs_yw_base_org k inner join orgNames c on c.i_orgid = k.i_orgpid ) select i_orgid,orgName from orgNames ;