Hierarchiczne uporządkowanie drzewa rodzic-dziecko

Muszę śledzić dane w SQL Server 2008 R2. SQLFiddle

Schemat:

CREATE TABLE [dbo]. [ICFilters] (
   [ICFilterID] [int] TOŻSAMOŚĆ (1,1) NIE NULL,
   [ParentID] [int] NOT NULL DEFAULT 0,
   [FilterDesc] [varchar] (50) NOT NULL,
   [Aktywny] [tinyint] NOT NULL DEFAULT 1,
 CONSTRAINT [PK_ICFilters] PODSTAWOWY KLUCZ ZESTAWIONY 
 ([ICFilterID] ASC) Z 
    PAD_INDEX = WYŁ.,
    STATISTICS_NORECOMPUTE = WYŁ.,
    IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
 ) W dniu [PODSTAWOWY]
) W dniu [PODSTAWOWY]

INSERT INTO [dbo]. [ICFilters] (ParentID, FilterDesc, Active)
Wartości 
(0, „typ produktu”, 1),
(1, „ProdSubType_1”, 1),
(1, „ProdSubType_2”, 1),
(1, „ProdSubType_3”, 1),
(1, „ProdSubType_4”, 1),
(2, „PST_1.1”, 1),
(2, „PST_1.2”, 1),
(2, „PST_1.3”, 1),
(2, „PST_1.4”, 1),
(2, „PST_1.5”, 1),
(2, „PST_1.6”, 1),
(2, „PST_1.7”, 0),
(3, „PST_2.1”, 1),
(3, „PST_2.2”, 0),
(3, „PST_2.3”, 1),
(3, „PST_2.4”, 1),
(14, „PST_2.2.1”, 1),
(14, „PST_2.2.2”, 1),
(14, „PST_2.2.3”, 1),
(3, „PST_2.8”, 1)

Stół:

| ICFILTERID | RODZICIEL | FILTERDESC | AKTYWNE |
--------------------------------------------------
| 1 | 0 | Rodzaj produktu | 1 |
| 2 | 1 | ProdSubType_1 | 1 |
| 3 | 1 | ProdSubType_2 | 1 |
| 4 | 1 | ProdSubType_3 | 1 |
| 5 | 1 | ProdSubType_4 | 1 |
| 6 | 2 | PST_1.1 | 1 |
| 7 | 2 | PST_1.2 | 1 |
| 8 | 2 | PST_1.3 | 1 |
| 9 | 2 | PST_1.4 | 1 |
| 10 | 2 | PST_1.5 | 1 |
| 11 | 2 | PST_1.6 | 1 |
| 12 | 2 | PST_1.7 | 0 |
| 13 | 3 | PST_2.1 | 1 |
| 14 | 3 | PST_2.2 | 0 |
| 15 | 3 | PST_2.3 | 1 |
| 16 | 3 | PST_2.4 | 1 |
| 17 | 14 | PST_2.2.1 | 1 |
| 18 | 14 | PST_2.2.2 | 1 |
| 19 | 14 | PST_2.2.3 | 1 |
| 20 | 3 | PST_2.8 | 1 |

Każdy wiersz ma identyfikator swojego rodzica i katalogu głównego parentid = 0. Są FilterDescto tylko przykładowe opisy, więc nie mogę parsować tych do zamówienia.

Pytanie

Czy można wybrać wszystkie wiersze w sposób podobny do drzewa? Jeśli tak to jak? Kiedy mówię „drzewko”, mam na myśli rekurencyjnie wybranie rodzica, a następnie wszystkich jego dzieci, a następnie wszystkich dzieci każdego z nich i tak dalej. Głębokie przejście przez drzewo.

Ja i moi przyjaciele próbowaliśmy, ale nie udało nam się rozwiązać problemu, ale nadal będziemy próbować. Jestem całkiem nowy w sql, więc może to można zrobić łatwo i po prostu robię wszystko trudniejszym niż to konieczne.

Przykład (pożądany) wynik:

| ICFILTERID | RODZICIEL | FILTERDESC | AKTYWNE |
--------------------------------------------------
| 1 | 0 | Rodzaj produktu | 1 |
| 2 | 1 | ProdSubType_1 | 1 |
| 6 | 2 | PST_1.1 | 1 |
| 7 | 2 | PST_1.2 | 1 |
| 8 | 2 | PST_1.3 | 1 |
| 9 | 2 | PST_1.4 | 1 |
| 10 | 2 | PST_1.5 | 1 |
| 11 | 2 | PST_1.6 | 1 |
| 12 | 2 | PST_1.7 | 0 |
| 3 | 1 | ProdSubType_2 | 1 |
| 13 | 3 | PST_2.1 | 1 |
| 14 | 3 | PST_2.2 | 0 |
| 17 | 14 | PST_2.2.1 | 1 |
| 18 | 14 | PST_2.2.2 | 1 |
| 19 | 14 | PST_2.2.3 | 1 |
| 15 | 3 | PST_2.3 | 1 |
| 16 | 3 | PST_2.4 | 1 |
| 20 | 3 | PST_2.8 | 1 |
| 4 | 1 | ProdSubType_3 | 1 |
| 5 | 1 | ProdSubType_4 | 1 |

OK, wystarczająca liczba komórek mózgowych jest martwa.

SQL Fiddle

WITH cte AS
(
  SELECT 
    [ICFilterID], 
    [ParentID],
    [FilterDesc],
    [Active],
    CAST(0 AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters]
  WHERE [ParentID] = 0
  UNION ALL
  SELECT 
    i.[ICFilterID], 
    i.[ParentID],
    i.[FilterDesc],
    i.[Active],  
    Level + CAST(i.[ICFilterID] AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters] i
  INNER JOIN cte c
    ON c.[ICFilterID] = i.[ParentID]
)

SELECT 
  [ICFilterID], 
  [ParentID],
  [FilterDesc],
  [Active]
FROM cte
ORDER BY [Level];

Powyższe wydaje mi się nie działać poprawnie. Wyobraź sobie konfigurację z dwoma tabelami z danymi typu Facebook. Tabela 1 zawiera PostId + inne pola. PostId to automatyczne zwiększanie i oczywiście w interfejsie posortujesz DESC, aby mieć najnowszy post na górze.

Teraz tabela komentarzy. Tabela 2 Ta tabela CommentId jest kluczem podstawowym, numerem auto. W swoim gui chcesz wyświetlić ASC, aby podczas czytania wątku miało to sens. (najstarsza (mniejsza liczba) u góry) Inne ważne klucze w tabeli 2 to: PostId (FK z powrotem do postów) i ParentId (FK do CommentId), gdzie ParentId będzie NULL, jeśli jest to komentarz „root” do posta. Jeśli ktoś ODPOWIADA na komentarz, wówczas parentId zostanie wypełniony komentarzem.
Mam nadzieję, że dostaniecie dryf. CTE będzie wyglądać następująco:

WITH  Comments
        AS ( SELECT  CommentId , ParentId, CAST(CommentId AS VARBINARY(MAX)) AS Sortkey, 0 AS Indent
             FROM    dbo.Comments
             WHERE   ParentId IS NULL AND PostId = 105
             UNION ALL
             SELECT  b.CommentId , b.ParentId,  c.Sortkey + CAST(b.CommentId AS varbinary(max))  AS Sortkey, c.Indent + 1 AS Indent
             FROM    dbo.Comments b
             INNER JOIN Comments c ON c.CommentId = b.ParentId
           )
   SELECT   *
   FROM     Comments
   ORDER BY Sortkey

Próbka wyjściowa

1   NULL    0x0000000000000001  0
5   1   0x00000000000000010000000000000001  1
6   5   0x000000000000000100000000000000010000000000000005  2
2   NULL    0x0000000000000002  0

Na F / B post 105 pojawiły się dwa komentarze (CommentIds 1 i 2) Ktoś następnie odpowiedział na Comment1 (CommentId 5, ParentId 1), a następnie ktoś inny skomentował tę odpowiedź, więc na Comment5 (CommentId 6, ParentId 6)

I viola, sekwencja jest poprawna, pod postem możesz teraz wyświetlać komentarze we właściwej kolejności. Aby wciąć posty, aby tworzyły się i konstruowały jak na Facebooku (im głębszy poziom, tym bardziej musi być margines od lewej), mam również kolumnę o nazwie Wcięcie. Korzenie są równe 0, a następnie w unii mamy c. Wcięcie + 1 AS Wcięcie W kodzie możesz teraz pomnożyć wcięcie przez załóżmy 32px i wyświetlaj komentarze w ładnej hierarchii i zarysie.

Nie widzę problemu z użyciem klucza podstawowego automatycznego przyrostu CommentId jako siły napędowej do zbudowania mojego klucza SortKey, ponieważ jest lepsza zmiana twojego zepsucia dat (komentowania) niż zepsucia klucza zarządzanego przez bazę danych, który zaczyna się od +1

create table pc ( parent varchar(10), child varchar(10) )

insert into pc values('a','b');
insert into pc values('a','c');
insert into pc values('b','e');
insert into pc values('b','f');
insert into pc values('a','d');
Insert into pc values('b','g');
insert into pc values('c','h');
insert into pc values('c','i');
insert into pc values('d','j');
insert into pc values('f','k');
insert into pc values('x','y');
insert into pc values('y','z');
insert into pc values('m','n');

 DECLARE @parent varchar(10) = 'a';
 WITH cte AS
 (
  select null parent, @parent child, 0 as level
   union
  SELECT  a.parent, a.child , 1 as level
    FROM pc a
   WHERE a.parent = @parent
   UNION ALL
  SELECT a.parent, a.child , c.level +    1
  FROM pc a JOIN cte c ON a.parent = c.child
  )
  SELECT distinct parent, child , level
  FROM cte
  order by level, parent

To da ci wszystkich potomków i poziom.
Mam nadzieję że to pomoże :)