Это не особенно эффективно, но если то, что вы хотите сделать, это эффективно «взорвать» всю иерархию и получить результаты последовательно от родителя к листу, что-то вроде этого сделает это:
WITH CategoryHierarchy AS
(
SELECT
ID, ParentCategoryIdFk, 0 AS Level,
ROW_NUMBER() OVER (ORDER BY ID) AS SubTreeID
FROM Category
WHERE CategoryID IN
(
SELECT pc.CategoryID
FROM Sale s
INNER JOIN Product p
ON p.saleidfk = s.id
INNER JOIN ProductCategory pc
ON pc.productid = p.id
WHERE s.id = @SaleID
)
UNION ALL
SELECT c.ID, c.ParentCategoryIdFk, h.Level + 1, h.SubTreeID
FROM CategoryHierarchy h
INNER JOIN Category c
ON c.ID = h.ParentID
)
SELECT c.ID, c.ParentCategoryIdFk AS ParentID, c.Name
FROM CategoryHierarchy h
INNER JOIN Category c
ON c.ID = h.ID
ORDER BY h.SubTreeID ASC, h.Level DESC
Это должно дать вам результаты, подобные следующим:
ID | ParentID | Name
---+----------+----------
1 | NULL | Men
2 | 1 | Shoes
3 | 2 | Sport
---+----------+----------
1 | NULL | Men
2 | 1 | Shoes
4 | 2 | Casual
---+----------+----------
1 | NULL | Men
5 | 1 | Watches
---+----------+----------
6 | NULL | Women
10 | 6 | Watches
Конечно, в реальных результатах не будет таких разделителей, я добавил их, чтобы сделать результаты более понятными.
Если вы не хотите, чтобы он был полностью разобран, вы можете использовать другой rownum, чтобы возвращать только первый экземпляр каждого родителя:
WITH CategoryHierarchy AS
(
SELECT
ID, ParentCategoryIdFk, 0 AS Level,
ROW_NUMBER() OVER (ORDER BY ID) AS SubTreeID
FROM Category
WHERE CategoryID IN
(
SELECT pc.CategoryID
FROM Sale s
INNER JOIN Product p
ON p.saleidfk = s.id
INNER JOIN ProductCategory pc
ON pc.productid = p.id
WHERE s.id = @SaleID
)
UNION ALL
SELECT c.ID, c.ParentCategoryIdFk, h.Level + 1, h.SubTreeID
FROM CategoryHierarchy h
INNER JOIN Category c
ON c.ID = h.ParentID
),
Filter_CTE AS
(
SELECT
ID, Level, SubTreeID
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY SubTreeID) AS RowNum
FROM CategoryHierarchy
)
SELECT c.ID, c.ParentCategoryIdFk AS ParentID, c.Name
FROM Filter_CTE f
INNER JOIN Category c
ON c.ID = f.ID
WHERE f.RowNum = 1
ORDER BY f.SubTreeID ASC, f.Level DESC
... даст вам результаты, подобные:
ID | ParentID | Name
---+----------+----------
1 | NULL | Men
2 | 1 | Shoes
3 | 2 | Sport
4 | 2 | Casual
5 | 1 | Watches
6 | NULL | Women
10 | 6 | Watches
Примечание. Будьте осторожны со второй версией, так как она не обязательно возвращает результаты в иерархическом порядке. Так уж получилось, что эта версия работает, потому что сами идентификаторы находятся в иерархическом порядке. Вы можете обойти это ограничение, но это значительно усложнит и без того сложный запрос.
Вторая версия гарантирует, что основная категория всегда будет отображаться перед любой из ее подкатегорий, что хорошо, если вы планируете построить рекурсивную структуру данных с помощью словаря. Это просто может не подходить для более быстрого построения дерева на основе стека или прямой отчетности для пользователя. Для этих целей лучше использовать первую версию.
person
Aaronaught
schedule
12.04.2010
