一次偶然和别人交流问题,需要用到递归函数,做一次blog记录。
准备数据如下:
create table tt(
t1 text,
t2 text,
t3 text,
t4 text
)
insert into tt values('JS1','JS4','三通','直线');
insert into tt values('JS1','JS2','三通','阀门');
insert into tt values('JS2','JS1','阀门','三通');
insert into tt values('JS2','JS3','阀门','');
insert into tt values('JS3','JS2','','阀门');
insert into tt values('JS4','JS1','直线','三通');
insert into tt values('JS4','JS5','直线','直线');
insert into tt values('JS5','JS4','直线','直线');
insert into tt values('JS7','JS8','弯头','阀门');
insert into tt values('JS8','JS7','阀门','弯头');
insert into tt values('JS1','JS6','三通','弯头');
insert into tt values('JS6','JS1','弯头','三通');
insert into tt values('JS7','JS6','弯头','弯头');
insert into tt values('JS6','JS7','弯头','弯头');
递归写法:
with recursive r(t1,t2,t3,t4) as (
select * from tt where t1='JS1'
union --all 取消all排重
select t.* from tt t inner join r on t.t1 = r.t2 where r.t4<>'阀门' --不等于阀门继续递归
)
select * from r --where t4='阀门'
做个记录~