SQL用于识别树状结构中的重复项

I am looking for a solution for this (MS SQL 2008, btw):

我正在寻找一个解决方案(MS SQL 2008,顺便说一句):

ID | ParentID | Feature_1 | Feature_2 +-----+------------+------------+----------+ 1 | NULL | A | B 2 | 1 | A | B 3 | 1 | A | C 4 | 2 | A | C

ID | ParentID | Feature_1 | Feature_2 + ----- + ------------ + ------------ + ---------- + 1 | NULL | A | B 2 | 1 | A | B 3 | 1 | A | C 4 | 2 | A | C

Whenever a child (a record with a ParentID) has the same set of features (Feature_1 and Feature_2) than its parent, I want to ignore it, essentially not show it in my select *.

每当一个孩子(具有ParentID的记录)具有与其父亲相同的一组功能(Feature_1和Feature_2)时,我想忽略它,基本上不会在我的select *中显示它。

So the result set should be

所以结果集应该是

ID | ParentID | Feature_1 | Feature_2 +-----+------------+------------+----------+ 1 | NULL | A | B 3 | 1 | A | C 4 | 2 | A | C

ID | ParentID | Feature_1 | Feature_2 + ----- + ------------ + ------------ + ---------- + 1 | NULL | A | B 3 | 1 | A | C 4 | 2 | A | C

Note that ID=2 is dropped, but ID=4 is displayed because it has a different set of features than its parent had.

请注意,ID = 2会被删除,但会显示ID = 4,因为它具有与其父级不同的一组功能。

Any help would be much appreciated!

任何帮助将非常感激!

2 个解决方案

#1

SELECT
    Child.ID,
    Child.ParentID,
    Child.Feature_1,
    Child.Feature_2
FROM
    MyTable AS Child
    LEFT OUTER JOIN MyTable AS Parent
        ON Child.ParentID = Parent.ID
WHERE
    Parent.Feature_1 <> Child.Feature_1
    OR Parent.Feature_2 <> Child.Feature_2
    OR Child.ParentID IS NULL
ORDER BY
    Child.ID

#2

SELECT *
FROM table A
WHERE a.ParentID IS NULL OR NOT EXISTS (SELECT 1 
                 FROM table b
                 WHERE a.ParentID = b.ID
                 AND a.Feature_1 = b.Feature_1 AND a.Feature_2 = b.Feature_2)