Переключение на Главную Страницу Страницы: 1 ОтправитьПечать
Горячая тема (более 10 ответов) Родитель родителя в запросе (число прочтений - 3266 )
OnePrg
Junior Member
**
Отсутствует


1C++ rocks!

Сообщений: 66
Зарегистрирован: 07. Октября 2019
Родитель родителя в запросе
11. Июня 2020 :: 12:16
Печать  
Подскажите как правильно реализовать такую конструкцию

Код
Выбрать все
SELECT
     сТовар.ParentID as [Родитель1 $Справочник.Товар],
     сТовар.ParentID.ParentID as [Родитель2 $Справочник.Товар]
 



вот так правильно?

Код
Выбрать все
SELECT
     сТовар.ParentID as [Товар $Справочник.Товар],
     сТовар.ParentID as [Родитель_1 $Справочник.Товар],
     сТовар2.ID      as [Родитель_2 $Справочник.Товар],
     сТовар3.ID      as [Родитель_3 $Справочник.Товар],
     сТовар4.ID      as [Родитель_4 $Справочник.Товар],
     сТовар5.ID      as [Родитель_5 $Справочник.Товар],
     сТовар6.ID      as [Родитель_6 $Справочник.Товар],
     сТовар7.ID      as [Родитель_7 $Справочник.Товар],
     сТовар8.ID      as [Родитель_8 $Справочник.Товар]
FROM $Справочник.Товар as сТовар
LEFT JOIN $Справочник.Товар as сТовар2
    ON сТовар.ParentID = сТовар2.ID
LEFT JOIN $Справочник.Товар as сТовар3
    ON сТовар2.ParentID = сТовар3.ID
LEFT JOIN $Справочник.Товар as сТовар4
    ON сТовар3.ParentID = сТовар4.ID
LEFT JOIN $Справочник.Товар as сТовар5
    ON сТовар4.ParentID = сТовар5.ID
LEFT JOIN $Справочник.Товар as сТовар6
    ON сТовар5.ParentID = сТовар6.ID
LEFT JOIN $Справочник.Товар as сТовар7
    ON сТовар6.ParentID = сТовар7.ID
LEFT JOIN $Справочник.Товар as сТовар8
    ON сТовар7.ParentID = сТовар8.ID
 

  
Наверх
 
IP записан
 
Sserj
Junior Member
**
Отсутствует


1C++ rocks!

Сообщений: 77
Зарегистрирован: 25. Октября 2010
Re: Родитель родителя в запросе
Ответ #1 - 11. Июня 2020 :: 14:16
Печать  
Задачу опиши, может проще и правильней через CTE будет.
  
Наверх
 
IP записан
 
Djelf
God Member
*****
Отсутствует


Ubuntu + wine@etersoft
+ 1C 7.7

Сообщений: 634
Местоположение: Питер
Зарегистрирован: 02. Ноября 2007
Пол: Мужской
Re: Родитель родителя в запросе
Ответ #2 - 11. Июня 2020 :: 16:21
Печать  
Sserj писал(а) 11. Июня 2020 :: 14:16:
Задачу опиши, может проще и правильней через CTE будет.


Да и так уже понятно что через CTE будет быстрее, а если еще оконные функции подключить, то ИТ вообще не понадобится - sql сам все посчитает.
Сейчас вопрос не в эффективности, а в том сколько времени затратит (0) для сдачи отчета начальству.
Так что и CTE и Windows Functions, это "рыба на потом".
  
Наверх
www  
IP записан
 
OnePrg
Junior Member
**
Отсутствует


1C++ rocks!

Сообщений: 66
Зарегистрирован: 07. Октября 2019
Re: Родитель родителя в запросе
Ответ #3 - 12. Июня 2020 :: 06:29
Печать  
Sserj писал(а) 11. Июня 2020 :: 14:16:
Задачу опиши, может проще и правильней через CTE будет.


Задача в том. чтобы вывести отчёт по реализации товаров по первым двум уровням справочника товаров. Т.е. полный справочник (первые два уровня иерархии) и к нему левым соединением данные по реализации.

Я уже сделал всё по другому - спасибо за участие. А что такое СТЕ?
  
Наверх
 
IP записан
 
Sserj
Junior Member
**
Отсутствует


1C++ rocks!

Сообщений: 77
Зарегистрирован: 25. Октября 2010
Re: Родитель родителя в запросе
Ответ #4 - 12. Июня 2020 :: 09:07
Печать  
OnePrg писал(а) 12. Июня 2020 :: 06:29:
А что такое СТЕ?


Common Table Expressions
Позволяет делать рекурсивные запросы. Я обычно с их помощью делаю все иерархические отчеты.
Но правда запросы получаются 3-4-ех этажные.
  
Наверх
 
IP записан
 
OnePrg
Junior Member
**
Отсутствует


1C++ rocks!

Сообщений: 66
Зарегистрирован: 07. Октября 2019
Re: Родитель родителя в запросе
Ответ #5 - 12. Июня 2020 :: 12:35
Печать  
Sserj писал(а) 12. Июня 2020 :: 09:07:
Common Table Expressions


Что-то не нашёл как это делается применительно к 1С. Дай ссылки на примеры, пожалуйста, и на мануальчик, если есть.
  
Наверх
 
IP записан
 
Sserj
Junior Member
**
Отсутствует


1C++ rocks!

Сообщений: 77
Зарегистрирован: 25. Октября 2010
Re: Родитель родителя в запросе
Ответ #6 - 12. Июня 2020 :: 14:36
Печать  
Ну самое банальное дерево групп номенклатуры.

with ДеревоГрупп as (
select
  cast($ПустойИД as char(9)) as [Группа]
  , 0 as [Уровень]
  , cast('' as varchar(200)) as [Наименование]
  , cast('' as varchar(500)) as [Порядок]

union all

select
  товары.id
  , ДеревоГрупп.Уровень + 1
  , cast(REPLICATE('   ', ДеревоГрупп.Уровень) + товары.descr as varchar(200))
  , cast(ДеревоГрупп.Порядок + '|' + товары.descr + товары.id as varchar(500))
from
  $Справочник.Номенклатура as товары with (nolock,forceseek,index(pcode))
  join ДеревоГрупп on товары.parentid = ДеревоГрупп.Группа
where
  товары.isfolder = 1
)

select
  ДеревоГрупп.Уровень as [Уровень $Число]
  , ДеревоГрупп.Наименование as [Наименование]
  , ДеревоГрупп.Группа as [Группа $Справочник.Номенклатура]
from
  ДеревоГрупп
order by
  ДеревоГрупп.Порядок
  
Наверх
 
IP записан
 
Sserj
Junior Member
**
Отсутствует


1C++ rocks!

Сообщений: 77
Зарегистрирован: 25. Октября 2010
Re: Родитель родителя в запросе
Ответ #7 - 12. Июня 2020 :: 15:20
Печать  
А теперь немного треша Улыбка
Остатки с итогами по группам.
(Вставляю на прямую с 1С, чтобы скопипастить для теста)

Код
Выбрать все
запросODBC = СоздатьОбъект("ODBCRecordSet");
текстЗапроса = "
|set nocount on
|;
|select
|  рег.Номенклатура as [Товар]
|  , товары.parentid as [Родитель]
|  , товары.descr as [Наименование]
|  , рег.КоличествоОстаток as [Остаток]
|into #Остатки
|from
|  $РегистрОстатки.ОстаткиТМЦ(,,,(Номенклатура),(Количество)) as рег
|  join $Справочник.Номенклатура as товары with (nolock)
|  on рег.Номенклатура = товары.id
|;
|
|with ДеревоГрупп as (
|select
|  cast($ПустойИД as char(9)) as [Группа]
|  , 0 as [Уровень]
|  , cast('' as varchar(200)) as [Наименование]
|  , cast('' as varchar(500)) as [Порядок]
|
|union all
|
|select
|  товары.id
|  , ДеревоГрупп.Уровень + 1
|  , cast(REPLICATE('   ', ДеревоГрупп.Уровень) + товары.descr as varchar(200))
|  , cast(ДеревоГрупп.Порядок + '|' + товары.descr + товары.id as varchar(500))
|from
|  $Справочник.Номенклатура as товары with (nolock,forceseek,index(pcode))
|  join ДеревоГрупп on товары.parentid = ДеревоГрупп.Группа
|where
|  товары.isfolder = 1
|)
|
|, итогиГрупп as (
|select
|  Остатки.Родитель as [Группа]
|  , min(товары.parentid) as [Родитель]
|  , sum(Остатки.Остаток) as [Остаток]
|from
|  #Остатки as Остатки
|  join $Справочник.Номенклатура as товары with (nolock)
|  on Остатки.Родитель = товары.id
|group by Остатки.Родитель
|
|union all
|
|select
|  родители.id
|  , родители.parentid
|  , итогиГрупп.Остаток
|from
|  $Справочник.Номенклатура as родители with (nolock)
|  join итогиГрупп on родители.id = итогиГрупп.Родитель
|)
|
|select
|  ДеревоГрупп.Уровень as [Уровень $Число]
|  , ДеревоГрупп.Группа as [Родитель $Справочник.Номенклатура]
|  , ДеревоГрупп.Наименование as [Родитель_Наименование]
|  , группыОстатков.Остаток as [Родитель_Остаток $Число]
|  , Остатки.Наименование as [Товар_Наименование]
|  , Остатки.Товар as [Товар $Справочник.Номенклатура]
|  , Остатки.Остаток as [Товар_Остаток $Число]
|from
|  ДеревоГрупп
|  join (
|    select
|      итогиГрупп.Группа as [Группа]
|      , sum(итогиГрупп.Остаток) as [Остаток]
|    from
|      итогиГрупп
|    group by
|      итогиГрупп.Группа
|   ) as группыОстатков on ДеревоГрупп.Группа = группыОстатков.Группа
|  left join #Остатки as Остатки on группыОстатков.Группа = Остатки.Родитель
|order by
|  ДеревоГрупп.Порядок, Остатки.Наименование, Остатки.Товар
|;
|
|drop table #Остатки
|;
|
|set nocount off
|";
тзРезультат = запросODBC.ВыполнитьИнструкцию(текстЗапроса);
//тзРезультат // : ТаблицаЗначений
 

  
Наверх
 
IP записан
 
OnePrg
Junior Member
**
Отсутствует


1C++ rocks!

Сообщений: 66
Зарегистрирован: 07. Октября 2019
Re: Родитель родителя в запросе
Ответ #8 - 15. Июня 2020 :: 05:54
Печать  
Понял - вложенные запросы, спасибо.
  
Наверх
 
IP записан
 
Sserj
Junior Member
**
Отсутствует


1C++ rocks!

Сообщений: 77
Зарегистрирован: 25. Октября 2010
Re: Родитель родителя в запросе
Ответ #9 - 15. Июня 2020 :: 06:31
Печать  
OnePrg писал(а) 15. Июня 2020 :: 05:54:
Понял - вложенные запросы, спасибо.


Вообще не правильно понял Улыбка
Можно использовать просто для улучшения визуальности, но самая главная сила именно в рекурсивности.
Вот в премере дерева групп, часть после union all будет выполняться столько раз сколько она вернет результат, причем в каждом следующем шаге в качестве соединения с ДеревоГрупп будет именно результат предыдущего выполнения.
  
Наверх
 
IP записан
 
Djelf
God Member
*****
Отсутствует


Ubuntu + wine@etersoft
+ 1C 7.7

Сообщений: 634
Местоположение: Питер
Зарегистрирован: 02. Ноября 2007
Пол: Мужской
Re: Родитель родителя в запросе
Ответ #10 - 15. Июня 2020 :: 06:47
Печать  
Sserj
А чего это у тебя старый вариант выборки?
Ты же нашел более изящьный вариант, без сложения 100500 строк в мега строку Порядок: http://www.1cpp.ru/forum/YaBB.pl?num=1214205575/982#982

OnePrg
Загляни туда, там я следующим постом нарисовал расшифровку на языке 1С того как этот CTE запрос работает.
  
Наверх
www  
IP записан
 
Sserj
Junior Member
**
Отсутствует


1C++ rocks!

Сообщений: 77
Зарегистрирован: 25. Октября 2010
Re: Родитель родителя в запросе
Ответ #11 - 15. Июня 2020 :: 07:19
Печать  
Djelf писал(а) 15. Июня 2020 :: 06:47:
Sserj
А чего это у тебя старый вариант выборки?
Ты же нашел более изящьный вариант, без сложения 100500 строк в мега строку Порядок: http://www.1cpp.ru/forum/YaBB.pl?num=1214205575/982#982


Н sqlite это работало, но много воды уже утекл все остатки 7-ки только в SQL остались, а в нем такой фокус не прокатывает.
  
Наверх
 
IP записан
 
Djelf
God Member
*****
Отсутствует


Ubuntu + wine@etersoft
+ 1C 7.7

Сообщений: 634
Местоположение: Питер
Зарегистрирован: 02. Ноября 2007
Пол: Мужской
Re: Родитель родителя в запросе
Ответ #12 - 15. Июня 2020 :: 07:28
Печать  
Sserj писал(а) 15. Июня 2020 :: 07:19:
Н sqlite это работало, но много воды уже утекл все остатки 7-ки только в SQL остались, а в нем такой фокус не прокатывает.

Спасибо за предупреждение, на MSSql не проверял.
Видимо SQLite, как я написал в расшифровке, сортирует порционно, а MSSql уже после окончания обработки CTE. Печально.

P.S. Нашел, вроде вот так выкрутится можно в MSSql: https://stackoverflow.com/questions/47171039/tsql-order-by-clause-in-a-cte-expre...
А в sqlite так нельзя, т.к. оконные функции в рекурсивных запросах запрещены.
  
Наверх
www  
IP записан
 
Sserj
Junior Member
**
Отсутствует


1C++ rocks!

Сообщений: 77
Зарегистрирован: 25. Октября 2010
Re: Родитель родителя в запросе
Ответ #13 - 15. Июня 2020 :: 09:46
Печать  
А я на самом деле эти дикие мегастроки и не использую, в mssql с этим же намного проще. Где то там при начале работы системы есть такое:
Код
Выбрать все
запросODBC.Выполнить("
|CREATE OR ALTER FUNCTION dbo.ДеревоНоменклатуры()
|RETURNS TABLE
|RETURN (
|with ДеревоГрупп as (
|select
|  cast($ПустойИД as char(9)) as [Группа]
|  , 0 as [Уровень]
|  , cast('' as char(80)) as [Наименование]
|  , cast('' as varchar(500)) as [Порядок]
|
|union all
|
|select
|  товары.id
|  , ДеревоГрупп.Уровень + 1
|  , товары.descr
|  , cast(ДеревоГрупп.Порядок + '|' + товары.descr + товары.id as varchar(500))
|from
|  $Справочник.Номенклатура as товары with (nolock,forceseek,index(pcode))
|  join ДеревоГрупп on товары.parentid = ДеревоГрупп.Группа
|where
|  товары.isfolder = 1
|)
|
|select
|  Группа, Наименование, Уровень, row_number() over (order by Порядок) as Порядок
|from
|  ДеревоГрупп
|)
|");
 



Потом где нужно просто использую что-то типа такого:

Код
Выбрать все
select * from ДеревоНоменклатуры() order by порядок
 


  
Наверх
 
IP записан
 
Djelf
God Member
*****
Отсутствует


Ubuntu + wine@etersoft
+ 1C 7.7

Сообщений: 634
Местоположение: Питер
Зарегистрирован: 02. Ноября 2007
Пол: Мужской
Re: Родитель родителя в запросе
Ответ #14 - 15. Июня 2020 :: 11:20
Печать  
Sserj писал(а) 15. Июня 2020 :: 09:46:
А я на самом деле эти дикие мегастроки и не использую, в mssql с этим же намного проще. Где то там при начале работы системы есть такое:


Да не сильно то и мегастроки получаются, я утрировал проблему.
У меня на 5100 записей около мегабайта лишних выходит, это ни о чем.
Правда на 10% медленнее, это немного хуже.
  
Наверх
www  
IP записан
 
Переключение на Главную Страницу Страницы: 1
ОтправитьПечать