我正在为一个遗留系统工作一个新功能.该系统使用两个表来保存"文档"和保存文档之间的关系("relation").这个"关系"表创建了类似于树形 struct 的东西.
我们需要列出所有被分类为有效类型的文档,并且没有任何其他有效类型的文档作为其祖先.
文档(约5亿条记录在生产中)
id: | type
1 invalid_type
2 valid_type_a
3 invalid_type
4 valid_type_a
5 invalid_type
6 valid_type_b
7 invalid_type
8 valid_type_b
9 invalid_type
10 valid_type_a
11 valid_type_a
12 invalid_type
13 invalid_type
14 invalid_type
15 valid_type_b
关系(约5亿条记录在生产中)
relationId | parentDocumentId | childDocumentId
1 1 2
2 1 3
3 2 4
4 2 5
5 3 6
6 6 7
7 8 9
8 9 10
9 12 13
10 13 14
11 13 15
在这些表中,我需要列出所有有效类型的文档,并且没有任何有效类型的祖先文档(在任何级别).
预期结果为:2、6、8、11、15
4是一个有效的文档,但有2作为它的父级.
10是一个有效的文件,但有8作为它的祖先.
虽然我最终可以改变这个 struct ,甚至可以将它迁移到另一个数据库(nosql...),现在的重点是利用现有的模式开发一个新的功能.
我一直在玩递归查询,但还不能把所有的东西放在一起. 我还可以在某种程度上 Select 和过滤记录,然后在代码中应用其余的规则.
任何指向任何方向的帮助或提示都非常感谢.
CREATE TABLE IF NOT EXISTS document
(
id int not null,
type varchar not null,
CONSTRAINT document_pk PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS relation
(
id int not null,
parent_id int not null,
child_id int not null,
CONSTRAINT relation_pk PRIMARY KEY (id),
CONSTRAINT parent_fk FOREIGN KEY (parent_id)
REFERENCES document (id),
CONSTRAINT child_fk FOREIGN KEY (child_id)
REFERENCES document (id)
);
INSERT INTO document (id, type)
VALUES
(1, 'invalid_type'),
(2, 'valid_type_a'),
(3, 'invalid_type'),
(4, 'valid_type_a'),
(5, 'invalid_type'),
(6, 'valid_type_b'),
(7, 'invalid_type'),
(8, 'valid_type_b'),
(9, 'invalid_type'),
(10, 'valid_type_a'),
(11, 'valid_type_a'),
(12, 'invalid_type'),
(13, 'invalid_type'),
(14, 'invalid_type'),
(15, 'valid_type_b');
INSERT INTO relation (id, parent_id, child_id)
VALUES
(1, 1, 2),
(2, 1, 3),
(3, 2, 4),
(4, 3, 5),
(5, 3, 6),
(6, 6, 7),
(7, 8, 9),
(8, 9, 10),
(9, 12, 13),
(10, 13, 14),
(11, 13, 15);