Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе  [new]
Valery_B
Member

Откуда: Москва
Сообщений: 2014
Добрый день, может кто помочь упорядочить стандартную древовидную структуру из Id+OwnerId ?
Только есть одно "Но".
В этой таблице есть ещё одно поле - OrderId, которое верно только в рамках одного узла.
И выборка должна быть в соответствии с OrderId.

Уже день маюсь над этим.
Почти получилось, только когда CONVERT(VARCHAR(MAX),OrderId) = 100, то порядок идёт не правильный.
Служба безопасности должна быть в самом конце.

Caption Path
Руководство1
Служба безопасности100
Отдел кадров2
Управление бездельников3
Отдел лодырей3\1
Группа бородатых лодырей3\1\1
Отдел лентяев3\2
Группа бородатых тунеядцев3\2\1
Группа бородатых лентяев3\2\2

Пример SQL:
+
DECLARE @Tmp TABLE
(
Id int PRIMARY KEY,
OwnerId int,
Caption VARCHAR(32),
OrderId int
)

INSERT INTO @Tmp VALUES (1,NULL, 'Служба безопасности',100)
INSERT INTO @Tmp VALUES (2,NULL, 'Отдел кадров',2)
INSERT INTO @Tmp VALUES (3,NULL, 'Управление бездельников',3)
INSERT INTO @Tmp VALUES (4,3, 'Отдел лентяев',2)
INSERT INTO @Tmp VALUES (5,3, 'Отдел лодырей',1)
INSERT INTO @Tmp VALUES (6,4, 'Группа бородатых лентяев',2)
INSERT INTO @Tmp VALUES (7,4, 'Группа бородатых тунеядцев',1)
INSERT INTO @Tmp VALUES (8,NULL, 'Руководство',1)
INSERT INTO @Tmp VALUES (9,5, 'Группа бородатых лодырей',1)


;WITH Rec AS 
 (
   SELECT t.*, 0 as Depth, CONVERT(VARCHAR(MAX),OrderId) as [Path]
   FROM @Tmp t
   WHERE OwnerId IS NULL

   UNION ALL

   SELECT t.*, Depth + 1, r.Path+'\'+CONVERT(VARCHAR(MAX), t.OrderId)
   FROM @Tmp t
   INNER JOIN Rec r ON t.OwnerId=r.Id
 )

SELECT Id, OwnerId, SPACE(Depth*3) + Caption, Path
FROM Rec
ORDER BY Path
15 окт 19, 10:02    [21994308]     Ответить | Цитировать Сообщить модератору
 Re: Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе  [new]
Valery_B
Member

Откуда: Москва
Сообщений: 2014
Соответсвенно, должно получится:

Caption Path
Руководство1
Отдел кадров2
Управление бездельников3
  Отдел лодырей3\1
    Группа бородатых лодырей3\1\1
  Отдел лентяев3\2
    Группа бородатых тунеядцев3\2\1
    Группа бородатых лентяев3\2\2
Служба безопасности100
15 окт 19, 10:07    [21994312]     Ответить | Цитировать Сообщить модератору
 Re: Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20222
Valery_B
Служба безопасности должна быть в самом конце.
ORDER BY CASE WHEN Path='100' THEN 1 ELSE 0 END, Path
15 окт 19, 10:07    [21994313]     Ответить | Цитировать Сообщить модератору
 Re: Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе  [new]
Valery_B
Member

Откуда: Москва
Сообщений: 2014
Akina,

И как это будет работать, когда добавят OrderID = 101 ?
INSERT INTO @Tmp VALUES (10,NULL, 'Юристы',101)
15 окт 19, 10:13    [21994319]     Ответить | Цитировать Сообщить модератору
 Re: Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20222
Valery_B
как это будет работать, когда добавят OrderID = 101 ?
Ну добавь да попробуй, чего спрашивать-то? тем более что у тебя и скрипты есть готовые, только ещё запись допиши...
15 окт 19, 10:18    [21994330]     Ответить | Цитировать Сообщить модератору
 Re: Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе  [new]
andy st
Member

Откуда:
Сообщений: 796
Valery_B,
+
;WITH Rec AS 
 (
   SELECT t.*, 0 as Depth, right(replicate('0',20)+CONVERT(VARCHAR(MAX),OrderId),20) as [Path]
   FROM @Tmp t
   WHERE OwnerId IS NULL

   UNION ALL

   SELECT t.*, Depth + 1, r.Path+'\'+right(replicate('0',20)+CONVERT(VARCHAR(MAX), t.OrderId),20)
   FROM @Tmp t
   INNER JOIN Rec r ON t.OwnerId=r.Id
 )

SELECT Id, OwnerId, SPACE(Depth*3) + Caption, Path
FROM Rec
ORDER BY Path
15 окт 19, 10:25    [21994340]     Ответить | Цитировать Сообщить модератору
 Re: Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе  [new]
Valery_B
Member

Откуда: Москва
Сообщений: 2014
Akina
Valery_B
как это будет работать, когда добавят OrderID = 101 ?
Ну добавь да попробуй, чего спрашивать-то? тем более что у тебя и скрипты есть готовые, только ещё запись допиши...

Странный ты.
Зачем ты тогда отвечаешь, если не знаешь, что будет ?
15 окт 19, 10:25    [21994341]     Ответить | Цитировать Сообщить модератору
 Re: Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе  [new]
Valery_B
Member

Откуда: Москва
Сообщений: 2014
andy st,

Спасибо!
Вроде работает, хотя до конца не понял, как :)
15 окт 19, 10:31    [21994345]     Ответить | Цитировать Сообщить модератору
 Re: Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30825
Valery_B
andy st,

Спасибо!
Вроде работает, хотя до конца не понял, как :)
Это CTE - рекурсивное табличное выражение, используется для построения полного пути к элементу, в виде строки. И потом по пути делается сортировка.
15 окт 19, 10:36    [21994351]     Ответить | Цитировать Сообщить модератору
 Re: Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе  [new]
Valery_B
Member

Откуда: Москва
Сообщений: 2014
alexeyvg,

Да, я знаю про CTE, я его здесь и написал :)

Обычно в Path стоит название, и ORDER BY идёт по названию.
В данном случае, ORDER BY должен идти по OrderId(в рамках каждого узла), а не по алфавиту.
15 окт 19, 10:40    [21994358]     Ответить | Цитировать Сообщить модератору
 Re: Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20222
Valery_B
Зачем ты тогда отвечаешь, если не знаешь, что будет ?
Я прекрасно знаю, что будет. Но я хочу, чтобы ты, начав получать ответы, не отключал напрочь думалку, а пытался понять, почему ответ - работает так, как ты хочешь.
15 окт 19, 10:52    [21994371]     Ответить | Цитировать Сообщить модератору
 Re: Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе  [new]
PaulWist
Member

Откуда:
Сообщений: 2204
Valery_B
andy st,

Спасибо!
Вроде работает, хотя до конца не понял, как :)


Path слева заполняется нулями + OrderId, затем берётся 20 символов справа, поэтому 0000003 < 0000100
15 окт 19, 11:24    [21994409]     Ответить | Цитировать Сообщить модератору
 Re: Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
andy st
Valery_B,
+
;WITH Rec AS 
 (
   SELECT t.*, 0 as Depth, right(replicate('0',20)+CONVERT(VARCHAR(MAX),OrderId),20) as [Path]
   FROM @Tmp t
   WHERE OwnerId IS NULL

   UNION ALL

   SELECT t.*, Depth + 1, r.Path+'\'+right(replicate('0',20)+CONVERT(VARCHAR(MAX), t.OrderId),20)
   FROM @Tmp t
   INNER JOIN Rec r ON t.OwnerId=r.Id
 )

SELECT Id, OwnerId, SPACE(Depth*3) + Caption, Path
FROM Rec
ORDER BY Path

Решение, конечно, правильное, но мне прям как то некомфортно становится, когда строковое представление чисел используют для задач сортировки... Хоть это и не слишком критично - мы даже не сохраняем результат, предлагаю поэкономить и конвертировать в varbinary:

;WITH Rec AS 
 (
   SELECT t.*, 0 as Depth, CONVERT(varbinary(max),OrderId) as [Path]
   FROM @Tmp t
   WHERE OwnerId IS NULL

   UNION ALL

   SELECT t.*, Depth + 1, r.Path+CONVERT(varbinary(max), t.OrderId)
   FROM @Tmp t
   INNER JOIN Rec r ON t.OwnerId=r.Id
 )

SELECT Id, OwnerId, SPACE(Depth*3) + Caption, Path
FROM Rec
ORDER BY Path
15 окт 19, 13:06    [21994535]     Ответить | Цитировать Сообщить модератору
 Re: Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30825
Valery_B
Обычно в Path стоит название, и ORDER BY идёт по названию.
В данном случае, ORDER BY должен идти по OrderId(в рамках каждого узла), а не по алфавиту.
в Path можно записать что угодно - то есть то, по чему нужно сортировать.
Раз нужно по OrderId, значит, записать OrderId
Конечно, дополнив OrderId ведущими нулями (или любым символом, который <= "0").
15 окт 19, 13:12    [21994547]     Ответить | Цитировать Сообщить модератору
 Re: Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе  [new]
Valery_B
Member

Откуда: Москва
Сообщений: 2014
Minamoto
Решение, конечно, правильное, но мне прям как то некомфортно становится, когда строковое представление чисел используют для задач сортировки...

Я конечно согласен, что ORDER BY <VARCHAR> не очень.
Но в таком случае, можно вывести как понятную для человека строку Path.

Пусть сервер хоть задымится, сортируя по этому полю(он делает это раза 2-3 в день на таблице в 1000 строк)
Зато, не задымится мозг у другого разработчика, который в первый раз это увидит.
15 окт 19, 15:23    [21994719]     Ответить | Цитировать Сообщить модератору
 Re: Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Valery_B
Minamoto
Решение, конечно, правильное, но мне прям как то некомфортно становится, когда строковое представление чисел используют для задач сортировки...

Я конечно согласен, что ORDER BY <VARCHAR> не очень.
Но в таком случае, можно вывести как понятную для человека строку Path.

Пусть сервер хоть задымится, сортируя по этому полю(он делает это раза 2-3 в день на таблице в 1000 строк)
Зато, не задымится мозг у другого разработчика, который в первый раз это увидит.

Хм, а что непонятного в значении 0x000000030000000100000001 после значения 0x0000000300000001?
ИМХО, даже незнакомый с логикой человек поймет смысл за довольно короткое время.
Я тоже увидел этот прием первый раз здесь на форуме - запустил для проверки, конечно, но ничего страшного в нем не увидел.
15 окт 19, 15:43    [21994738]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить