Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 SELECT всех подкатегорий для заданной  [new]
zalsily
Member

Откуда:
Сообщений: 104
Добрый день.

В СКЛ совсем недавно, но стоит задача выборки из единой таблицы.

Есть таблица Categories


id | name | createdate | parentid
-------------------------------------

где:
id - первичный ключ,
name - название категории,
createdate - время создания записи,
parentid - внешний ключ на родительскую категорию.

Но родительские категории находятся в этой же таблице, что и дочерние категории. Дочерние категории также могут выступать родительскими для других дочерних категорий. Каждая родительская категория может иметь несколько дочерних категорий, а те в свою очередь также могут иметь сколь угодно дочерних категорий. Уровень вложенности "дочерей" может быть 10 и более.

Не знаю как на счет скл-запроса, возможно ли рекурсии писать.

Так вот, требуется написать запрос по выборке всего дерева подкатегорий для заданной, указав значение поля id.

Помогите с запросом, пожалуйста.
2 сен 15, 17:19    [18100891]     Ответить | Цитировать Сообщить модератору
 Re: SELECT всех подкатегорий для заданной  [new]
zalsily
Member

Откуда:
Сообщений: 104
Да, забыл уточнить: в выборке следует также получить не только всю информацию о подкатегориях, но и значения полей name, createdate для данной категории, чей id перед началом поиска выступает входным параметром поиска.

То-есть, на входе мы знаем только id и по нему надо достать все записи, включая саму запись этого id.
2 сен 15, 17:23    [18100909]     Ответить | Цитировать Сообщить модератору
 Re: SELECT всех подкатегорий для заданной  [new]
zalsily
Member

Откуда:
Сообщений: 104
Получиться должно навигационное дерево (если это поможет понять картину).
2 сен 15, 17:24    [18100916]     Ответить | Цитировать Сообщить модератору
 Re: SELECT всех подкатегорий для заданной  [new]
iap
Member

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

не быстрее ли просто прочитать про рекурсивное CTE?

https://msdn.microsoft.com/ru-ru/library/ms175972(v=sql.120).aspx
https://msdn.microsoft.com/ru-ru/library/ms186243(v=sql.100).aspx
2 сен 15, 17:31    [18100952]     Ответить | Цитировать Сообщить модератору
 Re: SELECT всех подкатегорий для заданной  [new]
zalsily
Member

Откуда:
Сообщений: 104
Вы то правы, что самому читать разбираться надо, но задач стоит больше, чем есть времени. Понятно, что пока отвечают, сам не сижу на месте.
2 сен 15, 17:39    [18100995]     Ответить | Цитировать Сообщить модератору
 Re: SELECT всех подкатегорий для заданной  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8807
zalsily,

не переживайте, в хелпе и примеры есть.
2 сен 15, 18:20    [18101095]     Ответить | Цитировать Сообщить модератору
 Re: SELECT всех подкатегорий для заданной  [new]
zalsily
Member

Откуда:
Сообщений: 104
Написал кое-чего, но зацикленность появляется. Если здесь есть профи в скл, отзовитесь, покажу запрос, может и поверхностным взглядом сразу увидите где чего подправить. Сам пока не в состоянии с нуля что написать, пока могу только готовые править, дорабатывать примеры.
3 сен 15, 11:00    [18103040]     Ответить | Цитировать Сообщить модератору
 Re: SELECT всех подкатегорий для заданной  [new]
Glory
Member

Откуда:
Сообщений: 104751
zalsily
Сам пока не в состоянии с нуля что написать, пока могу только готовые править, дорабатывать примеры.

Ну так в данных выше ссылках есть готовые работающие примеры

"Г.Использование рекурсивного обобщенного табличного выражения для отображения нескольких уровней рекурсии
В следующем примере представлен иерархический список руководителей и служащих, отчитывающихся перед ними. Пример начинается с создания и заполнения таблицы dbo.MyEmployees."

"Е.Использование рекурсивного обобщенного табличного выражения для отображения иерархического списка
Следующий пример основан на примере Г с добавлением имен руководителей и служащих и соответствующих им должностей. Иерархия руководителей и служащих дополнительно выделяется с помощью соответствующих отступов на каждом уровне."
3 сен 15, 11:05    [18103088]     Ответить | Цитировать Сообщить модератору
 Re: SELECT всех подкатегорий для заданной  [new]
Макбет
Member

Откуда:
Сообщений: 72
zalsily,

пользуйся на здоровье

with q as (
select id, name, createdate, parentid, id as main_id from Categories
union all
select c.id, c.name, c.createdate, c.parentid, q.main_id as main_id from Categories c
inner join q on c.parentid=q.id)

select * from q where main_id=10
3 сен 15, 11:27    [18103264]     Ответить | Цитировать Сообщить модератору
 Re: SELECT всех подкатегорий для заданной  [new]
zalsily
Member

Откуда:
Сообщений: 104
Макбет, благодарю за помощь.

Не сочти за наглость, но есть еще одно обстоятельство в задаче (изначально не описал, думал потом сам допилю, но увы опять вывод ломается).

Таблица имеет еще одно поле
id | name | createdate | parentid | userid
-----------------------------------------------
, где userid - внешний ключ, указывающий к какому юзеру относится данная категория/подкатегория/подподкатегория...

Предоставленный ваш запрос получает в рекурсии все записи, относящиеся к входному значению id (по сути, мы получаем одну ветвь для начальной одной родительской категории).
А требуется получить все записи категорий/подкатегорий (в том числе высшего уровня) для данного юзера, то-есть по входящему значению userid. Таким образом, на клиенте требуется построить дерево категорий/подкатегорий для заданного юзера. Все, что мы знаем - это значение userid.

Если можешь, подсоби, пожалуйста.

И еще вопрос уже больше понятийный: какой интерес имеют здесь отвечающие и помогающие пользователи форума? Я понимаю, солидарность, чувство локтя, но по идее я никому еще помочь не могу, а мне здесь многие представляют решения задач, на которые сам бы тратил по 2-5 дней. Все ж люди тратят свое время, силы для разбора вопроса и построения решения. А ведь у каждого есть своя работа, свои задачи. В чем соль?
3 сен 15, 11:56    [18103452]     Ответить | Цитировать Сообщить модератору
 Re: SELECT всех подкатегорий для заданной  [new]
zalsily
Member

Откуда:
Сообщений: 104
Моя доработка запроса привела к тому, что вдвое больше проходов происходит по уже отработанным записям. Выборка идет правильно в отношении выборки только тех строк, у которых userid совпадает, но дочерние записи добавляются в выходной поток по нескольку раз.
3 сен 15, 12:01    [18103481]     Ответить | Цитировать Сообщить модератору
 Re: SELECT всех подкатегорий для заданной  [new]
Glory
Member

Откуда:
Сообщений: 104751
zalsily
Все, что мы знаем - это значение userid.

Т.е. вы не можете вместо where main_id=10 написать where userid=10 ?
3 сен 15, 12:01    [18103483]     Ответить | Цитировать Сообщить модератору
 Re: SELECT всех подкатегорий для заданной  [new]
zalsily
Member

Откуда:
Сообщений: 104
Срабатывает неверно!
3 сен 15, 12:05    [18103510]     Ответить | Цитировать Сообщить модератору
 Re: SELECT всех подкатегорий для заданной  [new]
Glory
Member

Откуда:
Сообщений: 104751
zalsily
Срабатывает неверно!

Хороший ответ.
А ваши критерии верно/неверно вы когда-нибудь огласите ?
3 сен 15, 12:06    [18103525]     Ответить | Цитировать Сообщить модератору
 Re: SELECT всех подкатегорий для заданной  [new]
zalsily
Member

Откуда:
Сообщений: 104
Ну я же написал. что несколько раз одни и те же строки попадают в выходной поток.

Ну смотрите, вывод записей такой:

id | mainid
--------------
1 | null
2 | 1
3 | 1
7 | 1
7 | 1
3 | 1
2 | 1

Последние три строки лишние, так как они уже присутствуют в выдаче.
3 сен 15, 12:10    [18103551]     Ответить | Цитировать Сообщить модератору
 Re: SELECT всех подкатегорий для заданной  [new]
zalsily
Member

Откуда:
Сообщений: 104
И это только в условии, что уровень вложенности подкатегорий только один.

Предполагаю, что при более глубокой вложенности, будет результат еще прикольнее :)
3 сен 15, 12:12    [18103560]     Ответить | Цитировать Сообщить модератору
 Re: SELECT всех подкатегорий для заданной  [new]
Glory
Member

Откуда:
Сообщений: 104751
zalsily
Ну я же написал. что несколько раз одни и те же строки попадают в выходной поток.

Т.е. вы предлагаете всем в голове смоделировать
- исходные данные
- текст вашего запроса
- желаемый "секретный" результат

и потом написать вам готовый работающий запрос ?
3 сен 15, 12:13    [18103566]     Ответить | Цитировать Сообщить модератору
 Re: SELECT всех подкатегорий для заданной  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8807
zalsily,

вероятно, Вы ошибаетесь, предполагая, что они лишние. В математике чудес не бывает.
3 сен 15, 12:14    [18103574]     Ответить | Цитировать Сообщить модератору
 Re: SELECT всех подкатегорий для заданной  [new]
zalsily
Member

Откуда:
Сообщений: 104
Нууу, да, ошибки только в наших ожиданиях, скл работает именно так как его написали. Тут я согласен.

Хорошо, я так понимаю, мне надо выложить всю таблицу с тестовыми данными, что бы всем стало ясно в чем же задача и почему работает запрос не так, как Я ожидаю?!

Дайте немного времени выложить сюда текст.
3 сен 15, 12:17    [18103592]     Ответить | Цитировать Сообщить модератору
 Re: SELECT всех подкатегорий для заданной  [new]
Макбет
Member

Откуда:
Сообщений: 72
zalsily,

1. запрос возвращает все ветки дочерних элементов для любого уровня вложенности (p.s. По умолчанию 100, см. maxrecursion), а не одну ветвь, если отбирать по main_id
если вы не ставите ограничение по корневой категории, то запрос выводит все возможные деревья
пример:
id parent id
1 null
2 1
3 2
на выходе будут 3 дерева, с 'корнями' в узлах 1,2 и 3
варианты:
- либо нужно выбрать узлы конкретного поддерева - фильтр по main_id
- либо вывести все поддеревья из корневых узлов
select  id, name, parentid, userid from q where main_id in (
select id from Categories where parentid is null)


2. не совсем понял суть, возможны варианты
вот пример для случая, когда нужно найти все дочерние категории для категорий, созданных/привязанных к пользователю
with q as (
select id, name, createdate, parentid, userid, userid as main_userid, id as main_id from Categories
union all
select c.id, c.name, c.createdate, c.parentid, c.userid, q.userid as main_userid, q.main_id as main_id from Categories c
inner join q on c.parentid=q.id)

select * from q where main_userid=2


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

select distinct id, name, parentid, userid from q where main_id in (
select main_id from q where main_userid=2)


3. помочь, проверить свои знания/навыки, потешить ЧСВ в конце концов Картинка с другого сайта.
3 сен 15, 12:20    [18103608]     Ответить | Цитировать Сообщить модератору
 Re: SELECT всех подкатегорий для заданной  [new]
zalsily
Member

Откуда:
Сообщений: 104
Запрос (поля name и createddate опущены для упрощения понимания):

with q as (
                            select id, userid, parentid, id as main_id from Categories
                            union all
                            select c.id, c.marketid, c.parentid, q.main_id as main_id from Categories c
                            inner join q on c.parentid=q.id)

                            select * from q where (userid=@userid)



Исходная таблица данных:

id | userid | parentid
===============
1 | 12 | NULL
2 | 19 | 3
3 | 19 | NULL
4 | 12 | 1
5 | 12 | 4
6 | 12 | 1
7 | 12 | 4
8 | 12 | 5
9 | 19 | 2
10 | 12 | 5
11 | 12 | NULL

К примеру, нас интересует дерево категорий для юзера 12.
Значит, полученный набор записей должен быть таким:

id | userid | parentid
===============
1 | 12 | NULL
4 | 12 | 1
5 | 12 | 4
6 | 12 | 1
7 | 12 | 4
8 | 12 | 5
10 | 12 | 5
11 | 12 | NULL
12 | 12 | 11

Гипотетическое дерево выглядит так:

1
|
|_4
| |
| |_5
| | |
| | |_8
| | |
| | |_10
| |
| |_7
|
|_6

11
|
|_12
3 сен 15, 12:39    [18103709]     Ответить | Цитировать Сообщить модератору
 Re: SELECT всех подкатегорий для заданной  [new]
Glory
Member

Откуда:
Сообщений: 104751
zalsily
Значит, полученный набор записей должен быть таким:

Как из 8ми исходных записей с userid=12 получается 9ть записей с userid=12 ?
3 сен 15, 12:44    [18103741]     Ответить | Цитировать Сообщить модератору
 Re: SELECT всех подкатегорий для заданной  [new]
zalsily
Member

Откуда:
Сообщений: 104
12 | 12 | 11 - лишняя, ошибочно добавил при редактировании сообщения. Пробелы все удаляются в браузере...

Но суть же понятна (учитывая ошибку)
3 сен 15, 12:47    [18103773]     Ответить | Цитировать Сообщить модератору
 Re: SELECT всех подкатегорий для заданной  [new]
Макбет
Member

Откуда:
Сообщений: 72
zalsily,

смотри выше
под цифрой 2
3 сен 15, 12:49    [18103800]     Ответить | Цитировать Сообщить модератору
 Re: SELECT всех подкатегорий для заданной  [new]
Glory
Member

Откуда:
Сообщений: 104751
zalsily
Но суть же понятна (учитывая ошибку)

Непонятно только, почему у вас(по вашим словам) не работает
with q as (
select id, userid, parentid, id as main_id from Categories
union all
select c.id, c.marketid, c.parentid, q.main_id as main_id from Categories c
inner join q on c.parentid=q.id)

select * from q where (userid=12)
3 сен 15, 12:50    [18103806]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить