代码之家 › 专栏 › 技术社区 › GollyJer

使用T-SQL过滤分层数据的最佳方法?

tsql database sql

GollyJer · 技术社区 · 15 年前

表1有一个项目列表。表2列出了项目可以关联的组。表3是1和2之间的交叉引用。

表2中的组是按层次结构设置的。

Key    ParentKey    Name
1      NULL         TopGroup1
2      NULL         TopGroup2
3      1            MiddleGroup1
4      2            MiddleGroup2
5      3            NextGroup1
6      4            NextGroup1
7      2            MiddleGroup3

我希望能够从表1中按表3筛选。
从表1中选择项,其中Table3.ParentKey不是“2” 或者它的任何后代 .

从另一个岗位 here on stack overflow 我已经能够使用CTE来识别层次结构。

WITH Parent AS
(
    SELECT
        table2.Key,
        cast(table2.Key as varchar(128))  AS Path
    FROM
        table2
    WHERE
        table2.ParentKey IS NULL

   UNION ALL

    SELECT
        TH.Key,
        CONVERT(varchar(128), Parent.Path + ',' + CONVERT(varchar(128),TH.Key)) AS Path
    FROM
        table2 TH
    INNER JOIN
        Parent
    ON
        Parent.Key = TH.ParentKey
)
SELECT * FROM Parent

我想这是一个由两部分组成的问题。

如何过滤上述内容?例如,返回TopGroup1不在沿袭中的所有组。
如何将其应用于交叉引用表1中的筛选结果。

2 回复 | 直到 15 年前

Niikola 15 年前

试试这个

-- Table1 (ItemKey as PK, rest of the columns)
-- Table2 (as you defined with Key as PK)
-- Table3 (ItemKey  as FK referencing Table1(ItemKey), 
--         GroupKey as FK referencing Table2(Key))

Declare @Exclude int
Set @Exclude = 2          
;WITH Groups AS     -- returns keys of groups where key is not equal
(                   -- to @Exclude or any of his descendants
   SELECT t.Key
     FROM table2 t
    WHERE t.ParentKey IS NULL
      and t.Key <> @Exclude
   UNION ALL
   SELECT th.Key,
     FROM table2 th
    INNER JOIN Groups g ON g.Key = th.ParentKey
    Where th.Key <> @Exclude
)
SELECT t1.* 
  FROM Table1 t1
 WHERE t1.key in (Select t3.ItemKey 
                    From table3 t3 
                   Inner Join Groups g2 
                      on t3.GroupKey = g2.Key
                 )

Sam Saffron James Allen 15 年前

关于这个主题有一整本书,请参阅: Joe Celko's Trees and Hierarchies in SQL for Smarties ’

就我个人而言,当我必须解决这个问题时,我使用一个临时表来展开层次结构,然后从临时表中选择一些内容。基本上,您可以在一个查询中在临时表中构建另一个层,通常层次结构只有5-10层深,所以您可以在5到10个查询中展开它。