Oracle SQL递归实现

最新推荐文章于 2023-02-02 09:14:28 发布

自由自在_Yu

最新推荐文章于 2023-02-02 09:14:28 发布

阅读量1.7k

点赞数

分类专栏：数据库文章标签： Oracle SQL递归实现 Oracle with递归

本文链接：https://blog.csdn.net/yuxiangaaaaa/article/details/77651898

版权

数据库专栏收录该内容

24 篇文章 0 订阅

订阅专栏

用户A推荐B,B推荐C,C推荐D、E和F用SQL计算出A推荐多少人？,C,D,E,F也算是A推荐的

drop table members;
create table members(
    id NUMBER(5) primary key not null,
    nick_name varchar(20) not null,
    parent_member varchar(20) 
);

insert into members(id,nick_name,parent_member) values(1,'A','');
insert into members(id,nick_name,parent_member) values(2,'B','A');
insert into members(id,nick_name,parent_member) values(3,'C','B');
insert into members(id,nick_name,parent_member) values(4,'D','C');
insert into members(id,nick_name,parent_member) values(5,'E','C');
insert into members(id,nick_name,parent_member) values(6,'F','C');

WITH TEMP(nick_name,parent_member) AS(  
SELECT nick_name,parent_member FROM members WHERE nick_name='A' 
UNION ALL 
SELECT b.nick_name,b.parent_member FROM members b, TEMP t WHERE b.parent_member=t.nick_name 
)  
SELECT count(*)-1 FROM TEMP;  
--或者
select count(*)-1
from members emp  
start with nick_name='A'  
connect by prior nick_name=parent_member;