如何使用Sqlite从递归CTW中排除条目?

CREATE TABLE GroupMembers (
    group_id        VARCHAR,
    member_id       VARCHAR
);

INSERT INTO GroupMembers(group_id, member_id) VALUES
    ('1', '10'),
    ('1', '20'),
    ('1', '30'),
    ('1', '-50'),
    ('2', '30'),
    ('2', '40'),
    ('3', '1'),
    ('3', '50'),
    ('4', '-10'),
    ('10', '50'),
    ('10', '60');


我想要一个查询,该查询将给我(递归)组中的成员列表。但是,第一个字符为“-”的成员表示减号后的id不在组中。

例如,“ 1”的成员是“ 10”,“ 20”,“ 30”和“ -50”。但是,“ 10”是一个组,因此我们需要添加其子级“ 50”和“ 60”。但是,“-50”已经是成员,因此我们不能包含“ 50”。总之,“ 1”的成员是“ 10”,“ 20”,“ 30”,“-50”和“ 60”。

看来此查询应该工作:

WITH RECURSIVE members(id) AS (
    VALUES('1')
    UNION
    SELECT gm.member_id
        FROM members m
        INNER JOIN GroupMembers gm ON mg.group_id=m.id
        LEFT OUTER JOIN members e ON '-' || gm.member_id=e.id
        WHERE e.id IS NULL
)
SELECT id FROM members;


但是我得到了错误:multiple references to recursive table: members

如何解决/重写此功能以执行我想要的操作?

注意:结果集中是否返回'-50'无关紧要。

最佳答案

我没有可用于测试的SQLite,但是假设-50也意味着也应排除50,我想您正在寻找的是:

WITH RECURSIVE members(id) AS (
    VALUES('1')
    UNION
    SELECT gm.member_id
    FROM GroupMembers gm
      JOIN members m ON gm.group_id=m.id
    WHERE member_id not like '-%'
      AND not exists (select 1
                      from groupMembers g2
                      where g2.member_id = '-'||gm.member_id)
)
SELECT id
FROM members;


(以上工作在Postgres中)

通常,您从递归部分的基表中进行选择,然后选择联接回实际的CTE。然后,通过常规的where子句对不需要的行进行过滤,而无需再次加入CTE。递归CTE定义为在JOIN没有找到更多行时终止。

SQLFiddle(Postgres):http://sqlfiddle.com/#!15/04405/1

需求更改后进行编辑(已进行了详细说明):

您需要根据行的位置排除行(原始问题中未提供的详细信息)。只能在CTE之外进行过滤。同样,我不能仅使用Postgres使用SQLite进行测试:

WITH RECURSIVE members(id, level) AS (
    VALUES('4', 1)
    UNION
    SELECT gm.member_id, m.level + 1
    FROM GroupMembers gm
      JOIN members m ON gm.group_id=m.id
)
SELECT m.id, m.level
FROM members m
where id not like '-%'
  and not exists (select 1
                  from members m2
                  where m2.level < m.level
                    and m2.id = '-'||m.id);


更新的SQLFiddle:http://sqlfiddle.com/#!15/ec0f9/3

关于sql - 如何从递归CTE中排除条目?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/23350853/

10-10 18:23
查看更多