如何使用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/