sql - 用 parentid 过滤掉表中的 child

Question

我需要一些帮助来构建一个查询，以便我过滤以下数据。

Table: MyTree
Id  ParentId  Visible
=====================
1   null      0
2   1         1
3   2         1
4   3         1
5   null      1
6   5         1

我希望查询得到以下结果:

Id  ParentId  Visible
=====================
5   null      1
6   5         1

也就是说，不应该返回隐藏节点的所有子节点。更重要的是，层次结构的深度不受限制。现在不要因为不可能的非显而易见的原因回答“只是将 2、3 和 4 设置为可见 = 0”......就像我正在修复一个可怕的“遗留系统”。

我在想这样的事情:

SELECT *
FROM MyTree m1
JOIN MyTree m2 ON m1.ParentId = m2.Id
WHERE m1.Visible = 1
AND (m1.ParentId IS NULL OR m2.Id IS NOT NULL)

抱歉任何语法错误

但这只会过滤第一级，对吗？希望你能帮忙。

编辑:完成标题，哎呀。该服务器是全新的 MSSQL 2008 服务器，但数据库以 2000 兼容模式运行。

Answer 1

我同意@Quassnoi 对递归 CTE(在 SQL Server 2005 或更高版本中)的关注，但我认为回答原始问题的逻辑不同:

WITH visall(id, parentid, visible) AS
   (SELECT  id, parentid, visible
    FROM    mytree
    WHERE   parentid IS NULL
        UNION ALL
    SELECT  m.id, m.parentid, m.visible & visall.visible AS visible
    FROM    visall
    JOIN    mytree m
      ON    m.parentid = visall.id
   )
SELECT  *
FROM    visall
WHERE   visall.visible = 1

表达相同逻辑的一种可能更优化的方法应该是尽可能多地在 WHERE 中进行可见检查——尽快停止沿着不可见的“子树”递归。 IE。:

WITH visall(id, parentid, visible) AS
   (SELECT  id, parentid, visible
    FROM    mytree
    WHERE   parentid IS NULL AND visible = 1
        UNION ALL
    SELECT  m.id, m.parentid, m.visible
    FROM    visall
    JOIN    mytree m
      ON    m.parentid = visall.id
    WHERE   m.visible = 1
   )
SELECT  *
FROM    visall

像往常一样处理性能问题，有必要在现实数据上对两个版本进行基准测试以自信地做出决定(这也有助于检查它们是否确实产生了相同的结果；-)——因为数据库引擎的优化器有时会出于奇怪的原因做奇怪的事情；- )。