sql - 检查基于 'ancestry' 的 SQL 表中的继承属性

Question

我正在使用 ancestry gem 来帮助组织我的应用程序在数据库中的树结构。它基本上将 child 的祖先信息写入一个名为“祖先”的特殊列。特定 child 的祖先列可能看起来像“1/34/87”，其中这个 child 的 parent 是 87，然后 87 的 parent 是 34，34 的是 1。

我们似乎可以从这个表中选择行，每个行都带有一个子查询，该子查询检查所有祖先以查看它是否设置了某个属性。例如。在我的应用程序中，您只需将父元素的可见性列设置为 0 即可隐藏项目及其子项。

我希望能够找到所有未隐藏祖先的元素。我尝试使用 REPLACE 命令将斜杠转换为逗号，但 IN 需要一组逗号分隔的整数，而不是一个带有逗号分隔字符串数字的字符串。

这很有趣，因为我可以分两步完成这个查询，例如检索该行，然后获取其祖先列，拆分出 id 并进行另一个查询以检查 id 是否在该 id 集合中并且可见性永远不会为 0 和 whala!但是将这些加入一个查询似乎是一项艰巨的任务。大量搜索显示了一些答案，但没有一个真正符合我的要求。

SELECT * FROM t1 WHERE id = 99;

99 的祖先列显示为“1/34/87”

SELECT * FROM t1 WHERE visibility = 0 AND id IN (1,34,87);

有点倒退，但如果这没有返回任何行，那么该项目是可见的。

有没有人遇到过这个问题并提出了解决方案。我真的不想走存储过程路线。它适用于 Rails 应用程序。

Answer 1

您可能想要做的是创建一个 Split 函数，如果您还没有一个 (Split a Delimited String in SQL )，然后将其用作 IN 选择。

还有另一种方法，但它可能会降低大型表的性能。有点像

SELECT  *
FROM    Table t INNER JOIN
        Table tParents      
        ON  (   t.Path LIKE CAST(tParents.ID AS VARCHAR(20)) + '/%'
                OR  t.Path LIKE +'%/' + CAST(tParents.ID AS VARCHAR(20)) + '/%'
                OR  t.Path LIKE +'%/' + CAST(tParents.ID AS VARCHAR(20)))

WHERE   t.ID = 99
AND     tParents.Visible = 0