ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе
16 сообщений из 16, страница 1 из 1
Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе
    #39876362
Valery_B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valery_B
Участник
Добрый день, может кто помочь упорядочить стандартную древовидную структуру из 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:
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
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

...
Рейтинг: 0 / 0
15.10.2019, 10:02
| Ответить | Цитировать | Написать  
Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе
    #39876366
Valery_B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valery_B
Участник
Соответсвенно, должно получится:

Caption PathРуководство1Отдел кадров2Управление бездельников3&nbsp&nbspОтдел лодырей3\1&nbsp&nbsp&nbsp&nbspГруппа бородатых лодырей3\1\1&nbsp&nbspОтдел лентяев3\2&nbsp&nbsp&nbsp&nbspГруппа бородатых тунеядцев3\2\1&nbsp&nbsp&nbsp&nbspГруппа бородатых лентяев3\2\2Служба безопасности100
...
Рейтинг: 0 / 0
15.10.2019, 10:07
| Ответить | Цитировать | Написать  
Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе
    #39876367
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Valery_BСлужба безопасности должна быть в самом конце.
Код: sql
1.
ORDER BY CASE WHEN Path='100' THEN 1 ELSE 0 END, Path
...
Рейтинг: 0 / 0
15.10.2019, 10:07
| Ответить | Цитировать | Написать  
Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе
    #39876371
Valery_B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valery_B
Участник
Akina,

И как это будет работать, когда добавят OrderID = 101 ?
Код: sql
1.
INSERT INTO @Tmp VALUES (10,NULL, 'Юристы',101)
...
Рейтинг: 0 / 0
15.10.2019, 10:13
| Ответить | Цитировать | Написать  
Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе
    #39876381
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Valery_Bкак это будет работать, когда добавят OrderID = 101 ?Ну добавь да попробуй, чего спрашивать-то? тем более что у тебя и скрипты есть готовые, только ещё запись допиши...
...
Рейтинг: 0 / 0
15.10.2019, 10:18
| Ответить | Цитировать | Написать  
Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе
    #39876390
andy st
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andy st
Гость
Valery_B,
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
;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

...
Рейтинг: 0 / 0
15.10.2019, 10:25
| Ответить | Цитировать | Написать  
Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе
    #39876391
Valery_B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valery_B
Участник
AkinaValery_Bкак это будет работать, когда добавят OrderID = 101 ?Ну добавь да попробуй, чего спрашивать-то? тем более что у тебя и скрипты есть готовые, только ещё запись допиши...
Странный ты.
Зачем ты тогда отвечаешь, если не знаешь, что будет ?
...
Рейтинг: 0 / 0
15.10.2019, 10:25
| Ответить | Цитировать | Написать  
Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе
    #39876393
Valery_B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valery_B
Участник
andy st,

Спасибо!
Вроде работает, хотя до конца не понял, как :)
...
Рейтинг: 0 / 0
15.10.2019, 10:31
| Ответить | Цитировать | Написать  
Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе
    #39876399
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Valery_Bandy st,

Спасибо!
Вроде работает, хотя до конца не понял, как :)Это CTE - рекурсивное табличное выражение, используется для построения полного пути к элементу, в виде строки. И потом по пути делается сортировка.
...
Рейтинг: 0 / 0
15.10.2019, 10:36
| Ответить | Цитировать | Написать  
Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе
    #39876405
Valery_B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valery_B
Участник
alexeyvg,

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

Обычно в Path стоит название, и ORDER BY идёт по названию.
В данном случае, ORDER BY должен идти по OrderId(в рамках каждого узла), а не по алфавиту.
...
Рейтинг: 0 / 0
15.10.2019, 10:40
| Ответить | Цитировать | Написать  
Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе
    #39876413
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Valery_BЗачем ты тогда отвечаешь, если не знаешь, что будет ?Я прекрасно знаю, что будет. Но я хочу, чтобы ты, начав получать ответы, не отключал напрочь думалку, а пытался понять, почему ответ - работает так, как ты хочешь.
...
Рейтинг: 0 / 0
15.10.2019, 10:52
| Ответить | Цитировать | Написать  
Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе
    #39876445
PaulWist
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PaulWist
Участник
Valery_Bandy st,

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

Path слева заполняется нулями + OrderId, затем берётся 20 символов справа, поэтому 0000003 < 0000100
...
Рейтинг: 0 / 0
15.10.2019, 11:24
| Ответить | Цитировать | Написать  
Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе
    #39876526
Minamoto
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Minamoto
Участник
andy stValery_B,
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
;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:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
;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
...
Рейтинг: 0 / 0
15.10.2019, 13:06
| Ответить | Цитировать | Написать  
Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе
    #39876531
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Valery_BОбычно в Path стоит название, и ORDER BY идёт по названию.
В данном случае, ORDER BY должен идти по OrderId(в рамках каждого узла), а не по алфавиту.в Path можно записать что угодно - то есть то, по чему нужно сортировать.
Раз нужно по OrderId, значит, записать OrderId
Конечно, дополнив OrderId ведущими нулями (или любым символом, который <= "0").
...
Рейтинг: 0 / 0
15.10.2019, 13:12
| Ответить | Цитировать | Написать  
Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе
    #39876624
Valery_B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valery_B
Участник
MinamotoРешение, конечно, правильное, но мне прям как то некомфортно становится, когда строковое представление чисел используют для задач сортировки...
Я конечно согласен, что ORDER BY <VARCHAR> не очень.
Но в таком случае, можно вывести как понятную для человека строку Path.

Пусть сервер хоть задымится, сортируя по этому полю(он делает это раза 2-3 в день на таблице в 1000 строк)
Зато, не задымится мозг у другого разработчика, который в первый раз это увидит.
...
Рейтинг: 0 / 0
15.10.2019, 15:23
| Ответить | Цитировать | Написать  
Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе
    #39876630
Minamoto
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Minamoto
Участник
Valery_BMinamotoРешение, конечно, правильное, но мне прям как то некомфортно становится, когда строковое представление чисел используют для задач сортировки...
Я конечно согласен, что ORDER BY <VARCHAR> не очень.
Но в таком случае, можно вывести как понятную для человека строку Path.

Пусть сервер хоть задымится, сортируя по этому полю(он делает это раза 2-3 в день на таблице в 1000 строк)
Зато, не задымится мозг у другого разработчика, который в первый раз это увидит.
Хм, а что непонятного в значении 0x000000030000000100000001 после значения 0x0000000300000001 ?
ИМХО, даже незнакомый с логикой человек поймет смысл за довольно короткое время.
Я тоже увидел этот прием первый раз здесь на форуме - запустил для проверки, конечно, но ничего страшного в нем не увидел.
...
Рейтинг: 0 / 0
15.10.2019, 15:43
| Ответить | Цитировать | Написать  
16 сообщений из 16, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как упорядочить дерево ? ORDER BY OrderID на рекурсивном запросе
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]