尝试基于SQL中的先前行父级递归选择行

问题描述

使用表ROLE(ROLE_ID，PARENT_ROLE_ID)如何获取所有依赖于另一行的行?例如，一个根级别角色的[PARENT_ROLE_ID]值为空，但是该根角色可以有很多孩子，而这些孩子又有很多孩子.如何在查询中处理此问题?我不断遇到一些选择，但无法正常工作. oracle在执行开始于"查询时所做的几乎是什么.任何帮助将不胜感激.我当前的示例(错误的)如下:

Using the table ROLE(ROLE_ID, PARENT_ROLE_ID) how can I get all rows that are dependent on another? For example, a root level role will have a null value for [PARENT_ROLE_ID], but that root role could have many children, who in turn have many children. How can I go about handling this in a query? I keep coming across some options but not working. It is pretty much what oracle does when doing a 'start with' query. Any help would be greatly appreciated. The current example (and wrong) that I have is below:

select * into #Collection from MCCApps.dbo.APP_ROLE r where r.APP_ROLE_ID = 1

declare @ParentId int = 1

while exists (select * from #Collection WHERE APP_PARENT_ID = @ParentId)
begin

select @ParentId = (select top 1 @ParentId
                   from #Collection
                   order by APP_ROLE_ID asc)

DELETE FROM MCCApps.dbo.APP_ROLE
where APP_PARENT_ID = @ParentId

结束

推荐答案

-- drop table role;
create table role
(   id int not null auto_increment primary key, -- for kicks and if i screw up
    roleId int not null,    -- i will manually provide it so i can see it
    parent int null,
    unique index (roleId)
);

insert role(roleId,parent) values (1,null),(2,null),(3,2),(4,3),(5,3),(6,3),(7,4),(8,3),(9,6),(10,6),(11,10);

-- if i want to delete roleId A then i need to delete anything with a parent=A (B) and all of B's lineage too

-- drop procedure deleteParentDownwards;
delimiter $$
create procedure deleteParentDownwards
(
deleteMe int
)
BEGIN
    -- deleteMe parameter means i am anywhere in hierarchy of role
    -- and i want me and all my offspring deleted (no orphaning of children or theirs)
    declare bDoneYet boolean default false;
    declare working_on int;
    declare theCount int;

    CREATE TABLE xxDeleteRoleHierarchyxx
    (
        roleId int not null,
        processed int not null
    );

    set bDoneYet=false;
    insert into xxDeleteRoleHierarchyxx (roleId,processed) select deleteMe,0;
    while (!bDoneYet) do
        select count(*) into theCount from xxDeleteRoleHierarchyxx where processed=0;

        if (theCount=0) then 
            -- found em all
            set bDoneYet=true;
        else
            -- one not processed yet, insert its children for processing
            SELECT roleId INTO working_on FROM xxDeleteRoleHierarchyxx where processed=0 limit 1;
            insert into xxDeleteRoleHierarchyxx (roleId,processed)
            select roleId,0 from role
            where parent=working_on;

            -- mark the one we "processed for children" as processed
            update xxDeleteRoleHierarchyxx set processed=1 where roleId=working_on;
        end if;
    end while;
    delete from role where roleId in (select roleId from xxDeleteRoleHierarchyxx);
    drop table xxDeleteRoleHierarchyxx;
END
$$

call deleteParentDownwards(3); -- deletes many

call deleteParentDownwards(6); -- deletes 4 (assuming line above was not called first!)

这篇关于尝试基于SQL中的先前行父级递归选择行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！