Использование SQL HierarchyID для выбора соответственно от дочерних элементов к родительским

Мне нужно написать хранимую процедуру, чтобы вернуть некоторые данные о заказе от детей к родителям. Как-то сложно описать, что я хочу сделать, но позвольте мне попробовать: представьте, что у нас есть эта иерархия, называемая Категории: Родитель > Ребенок1 > Ребенок2 > Child3, все сохранено с использованием SQL HierarchyID:

Category Table
--------------
Cat_ID             | Cat_Name
..............................
/1/                | News
/1/1/              | NewsOfUSA
/1/1/1/            | NewsOfWestUSA
/1/1/1/1/          | NewsOfWashington

И мы сохранили новости с этими категориями, как показано ниже:

News Table
-------------
News_ID     | FK_Cat_ID    | News_Content
.........................................
0001        | /1/          | one
0002        | /1/1/        | two
0003        | /1/1/1/      | three
0004        | /1/1/1/1/    | four1
0005        | /1/1/1/1/    | four2
0006        | /1/1/1/1/    | four3
0007        | /1/1/1/1/    | four4

И, наконец, я хочу выбрать для sxample Top Ten новости с этим условием:

Если NewsOfWashington содержит 10 новостей, выберите их, иначе выберите из NewsOfWestUSA, иначе выберите из NewsOfUSA, иначе выберите из News >, Пока не достигнешь десяти

И порядок выбора таков

four4,four3,four2,four1,three,two,one

Я пытался использовать рекурсивный CTE, но не смог найти правильный способ его реализации.


sql sql-server recursive-query hierarchyid
person Yasin    schedule 02.08.2016    source источник
comment
Используйте несколько запросов с UNION?   -  person Malk    schedule 02.08.2016
comment
@Malk, Нет Нет, только эти две таблицы, я хочу сначала проверить, достигают ли новости с тегом NewsOfWashington 10 или нет, если не выбирать из его родительского NewsOfWestUSA и так далее ...   -  person Yasin    schedule 02.08.2016

Ответы (3)


arrow_upward
0
arrow_downward

Полностью украсть сценарий данных из ответа Митча, это не так уж плохо:

CREATE TABLE Categories
    (
      CatID hierarchyid not null
                        primary key ,
      Name nvarchar(255) not null
    );
CREATE TABLE News
    (
      NewsID int not null
                 primary key ,
      CatID hierarchyid not null ,
      NewsContent nvarchar(max) not null
    );

INSERT INTO Categories
VALUES ('/1/', 'News'),
  ('/1/1/', 'NewsOfUSA'),
  ('/1/1/1/', 'NewsOfIndiana'),
  ('/1/2/', 'NewsOfUK');

INSERT INTO News
VALUES (1, '/1/', 'Aliens invaded'),
  (2, '/1/1/', 'Aliens invaded the US'),
  (3, '/1/1/1/', 'Aliens invaded the midwest'),
  (4, '/1/2/', 'Aliens invaded the UK');

-- actual answer begins here
select TOP(10) News.[NewsContent]
from dbo.Categories as parent
join dbo.Categories as child
    on child.CatID.IsDescendantOf(parent.CatID) = 1
join News
    on News.CatID = parent.CatID
WHERE child.Name = 'NewsOfIndiana'
order by News.CatID.GetLevel() DESC

По сути, я использую метод IsDescendentOf(), чтобы получить, к каким категориям принадлежит данная категория, а затем присоединяюсь к элементам новостей на основе этого нового списка категорий и, наконец, сортирую по методу GetLevel() (который возвращает, насколько глубоко в иерархии находится заданное значение). является).

person Ben Thul    schedule 02.08.2016
comment
Отличная работа! Как вы сказали, мы должны пометить ответ Митча как ответ, потому что он тоже ответил на него, но ваш ответ как-то проще и легче реализовать. Спасибо, бро. - person Yasin; 03.08.2016
comment
Вы можете нажать маленькую стрелку вверх рядом с его ответом, чтобы быстро повысить его репутацию. :) Так и сделал (спасибо за напоминание!). - person Ben Thul; 03.08.2016
comment
Я так и сделал, но моя репутация недостаточно высока, чтобы сразу отражать - person Yasin; 05.08.2016

arrow_upward
1
arrow_downward

Чтобы определить расстояние, найдите всех потомков, а затем отсортируйте по разнице в глубине:

USE tempdb;

CREATE TABLE Categories (CatID hierarchyid not null primary key, Name nvarchar(255) not null);
CREATE TABLE News (NewsID int not null primary key, CatID hierarchyid not null, NewsContent nvarchar(max) not null);

INSERT INTO Categories
VALUES ('/1/', 'News'),
  ('/1/1/', 'NewsOfUSA'),
  ('/1/1/1/', 'NewsOfIndiana'),
  ('/1/2/', 'NewsOfUK');

INSERT INTO News
VALUES (1, '/1/', 'Aliens invaded'),
  (2, '/1/1/', 'Aliens invaded the US'),
  (3, '/1/1/1/', 'Aliens invaded the midwest'),
  (4, '/1/2/', 'Aliens invaded the UK');

DECLARE @VisitorLocation hierarchyid = '/1/1/1/';

WITH 
relevantCategories AS (
    SELECT c.*, ABS(@VisitorLocation.GetLevel() - c.CatID.GetLevel()) as RelevanceDistance
    FROM Categories c
    WHERE @VisitorLocation.IsDescendantOf(c.CatID) = 1
)
SELECT TOP(10) n.*, c.RelevanceDistance
FROM relevantCategories c
INNER JOIN News n on n.CatID = c.CatID
ORDER BY RelevanceDistance ASC, n.NewsID DESC;

DROP TABLE Categories;
DROP TABLE News;

Производит:

  NewsID   CatID           NewsContent           RelevanceDistance  
 -------- -------- ---------------------------- ------------------- 
       3   0x5AD6   Aliens invaded the midwest                   0  
       2   0x5AC0   Aliens invaded the US                        1  
       1   0x58     Aliens invaded                               2
person Mitch    schedule 02.08.2016
comment
Я бы тоже хотел отметить ваш ответ как ответ, потому что это тоже очень хорошо, но, к сожалению, нельзя отметить более одного! Спасибо за Ваш ответ - person Yasin; 03.08.2016

arrow_upward
0
arrow_downward

Попробуйте выполнить следующее, он вернет ТОП-10 в том порядке, в котором вы хотите:

SELECT TOP 10 CASE n.FK_Cat_ID
WHEN '/1/1/1/1/' THEN 0 
WHEN '/1/1/1/' THEN 1
WHEN '/1/1/' THEN 2
WHEN '/1/' THEN 3
Else 4 END,*
FROM News as n
INNER JOIN Category as c
ON n.FK_Cat_ID = c.Cat_ID
ORDER BY 1;
person Slava Murygin    schedule 02.08.2016
comment
Я ценю вашу помощь, но проблема в том, что эти категории, 'NewsOfWashington' и т. д., являются динамическими, а у меня просто cat_id /1/1/1/1/ как это можно сделать таким образом? - person Yasin; 02.08.2016