主页 / dba / 问题 / 310914
Accepted
Henrik Halbritter
Asked: 2022-04-14 05:04:34 +0800 CST

递归查询 3 个表

  • 772

我有以下表结构

create table folder
(
    id   integer
        constraint folder_pk
            primary key,
    path TEXT
);

create index folder_path_uindex
    on folder (path);

create table membership
(
    id     integer
        constraint membership_pk
            primary key,
    member integer
        references user,
    groups integer
        references user
);

create index membership_group_member_uindex
    on membership (groups, member);

create table rule
(
    id        integer
        constraint rule_pk
            primary key,
    folder    integer
        references folder,
    principal integer
        references user,
    type      INTEGER,
    level     integer
);

create table user
(
    id   integer
        constraint user_pk
            primary key,
    name integer,
    type text
);

create index principal_name_type_uindex
    on user (name, type);

我需要递归地遍历组以查明用户是否有效地成为组的成员，然后加入规则表以获取与用户相关的所有规则。规则可以直接应用于用户或组。一个组可以是另一个组的成员。

我将如何开始，甚至在 SQLite 中是否可行？

我有点坚持这一点。任何输入表示赞赏

- 编辑 -

我想我找到了解决方案。我会写一个遮阳篷

sqlite recursive

1 个回答

  1. Best Answer

    我使用视图来连接用户和成员表，以便自行加入它们。

    CREATE VIEW RecursoinView as
SELECT u.id as UserID, u.name  as Name, m.groups as groups , u.type as type     FROM user u join membership m on u.id = m.member;

    之后，我user.id使用以下 SQL 语句进行查询：

    WITH    q AS 
        (
        SELECT  *
        FROM    RecursoinView
        WHERE   RecursoinView.UserID=398 
        UNION ALL
        SELECT  m.*
        FROM    RecursoinView m
        JOIN    q
        ON      m.UserID = q.groups
        )
SELECT  *
FROM    q join rule on q.groups = rule.principal join folder f on rule.folder = f.id
    • 0

相关问题