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

Откуда:
Сообщений: 6
Добрый день. Для работы требуется разворачивать блоки, в которые входят сборки, а в них подсборки и т.д. Вот только возникла проблема, что данный уровень запроса мне не по зубам. Сейчас это делается через спец. функцию, но она одноуровневая и решение данной задачи занимает несколько дней. Надеюсь на Вашу помощь.
Требуемая информация хранится в одной таблице ever. В ней десятки столбцов и около 1 млн. строк, но требуемые данные находятся в 2 столбцах: ever_parim и ever_detim
Для примера. Что имеем:

ever_parim	| ever _lineno	| ever _ondate	| ever _detim	
Корпус | 10 | 12.07.2018 | механизм
Корпус | 20 | 12.07.2018 | переключатель
переключатель | 10 | 12.07.2018 | винт
переключатель | 20 | 12.07.2018 | гайка
переключатель | 30 | 12.07.2018 | шайба
переключатель | 40 | 12.07.2018 | кнопка
механизм | 10 | 12.07.2018 | колонка
механизм | 20 | 12.07.2018 | водило
механизм | 30 | 12.07.2018 | обойма
колонка | 10 | 12.07.2018 | Сталь 3

Что требуется получить

ever_parim	| ever _lineno	| ever _ondate	| ever _detim
Корпус | 10 | 12.07.2018 | механизм
механизм | 10 | 12.07.2018 | колонка
колонка | 10 | 12.07.2018 | Сталь 3
механизм | 20 | 12.07.2018 | водило
механизм | 30 | 12.07.2018 | обойма
Корпус | 20 | 12.07.2018 | переключатель
переключатель | 10 | 12.07.2018 | винт
переключатель | 20 | 12.07.2018 | гайка
переключатель | 30 | 12.07.2018 | шайба
переключатель | 40 | 12.07.2018 | кнопка
8 июн 18, 14:57    [21479392]     Ответить | Цитировать Сообщить модератору
 Re: Дерево сборки  [new]
aleks222
Member

Откуда:
Сообщений: 926
Экстрасексы и телепузики последнее время совсем повывелись.
Придется те напрячься объяснить.

Каким образом из первого следует второе?
8 июн 18, 15:34    [21479522]     Ответить | Цитировать Сообщить модератору
 Re: Дерево сборки  [new]
Посетитель
Member

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

похоже на обход дерева в глубину
8 июн 18, 15:38    [21479531]     Ответить | Цитировать Сообщить модератору
 Re: Дерево сборки  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4537
Посетитель
aleks222,
похоже на обход дерева в глубину

А дерево-то где?
8 июн 18, 15:53    [21479570]     Ответить | Цитировать Сообщить модератору
 Re: Дерево сборки  [new]
aleks222
Member

Откуда:
Сообщений: 926
Посетитель
aleks222,

похоже на обход дерева в глубину


Так, телепузики подтянулись!
Ты не скромничай - ты все выкладывай. Как есть.
8 июн 18, 15:54    [21479577]     Ответить | Цитировать Сообщить модератору
 Re: Дерево сборки  [new]
Посетитель
Member

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

значит так. сначала было слово

Поиск в глубину

строка - ребро, ever_parim,ever_detim - пары вершин.
обход в порядке lineno начинай с узла, который есть только в parim
8 июн 18, 16:07    [21479607]     Ответить | Цитировать Сообщить модератору
 Re: Дерево сборки  [new]
Bob2012
Member

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

Есть головное изделие, допустим ТРИМ.468369.003-01 и мы делаем запрос
select *
from ever
where ever_parim = 'ТРИМ.468369.003-01'

Получаем все изделия которые в него входят (здесь только часть)
ever_parim	       ever_lineno		ever_ondate		ever_detim
ТРИМ.468369.003-01 10 1899-12-31 00:00:00.000 ТРИМ.301412.022-01
ТРИМ.468369.003-01 20 1899-12-31 00:00:00.000 ТРИМ.301412.023
ТРИМ.468369.003-01 30 1899-12-31 00:00:00.000 ТРИМ.321455.013-02
ТРИМ.468369.003-01 40 1899-12-31 00:00:00.000 6Д6.832.046
ТРИМ.468369.003-01 50 1899-12-31 00:00:00.000 6В8.840.031-01

Теперь разворачиваем первое изделие которое в него входит
select *
from ever
where ever_parim ='ТРИМ.301412.022-01'

Получаем
ТРИМ.301412.022-01    	10	1899-12-31 00:00:00.000	ТРИМ.687281.210       
ТРИМ.301412.022-01 20 1899-12-31 00:00:00.000 ТРИМ.687281.211
ТРИМ.301412.022-01 30 1899-12-31 00:00:00.000 ТРИМ.687281.212
ТРИМ.301412.022-01 40 1899-12-31 00:00:00.000 ТРИМ.687281.213

Дальше
select *
from ever
where ever_parim ='ТРИМ.687281.210'

ТРИМ.687281.210       	20	1899-12-31 00:00:00.000	6Д8.128.041-05        
ТРИМ.687281.210 30 1899-12-31 00:00:00.000 2-0,8-2,5-7Л6307
ТРИМ.687281.210 40 1899-12-31 00:00:00.000 6212746747
ТРИМ.687281.210 50 1899-12-31 00:00:00.000 6349555475

select *
from ever
where ever_parim ='6Д8.128.041-05'

6Д8.128.041-05        	10	1899-12-31 00:00:00.000	22000016000       


И вот мы в конце
А хотелось бы сразу получить
ever_parim	ever_lineno	ever_ondate	ever_detim
ТРИМ.468369.003-01 10 1899-12-31 00:00:00.000 ТРИМ.301412.022-01
ТРИМ.301412.022-01 10 1899-12-31 00:00:00.000 ТРИМ.687281.210
ТРИМ.687281.210 20 1899-12-31 00:00:00.000 6Д8.128.041-05
6Д8.128.041-05 10 1899-12-31 00:00:00.000 22000016000
ТРИМ.468369.003-01 20 1899-12-31 00:00:00.000 ТРИМ.301412.023
- (это следующее изделие из первого запроса)
и дальше разворачивать уже второе изделие, далее 3 и т.д.
Потом мы добавим критерии отбора и это сократит выборку, но это если получится сделать такой запрос

Сообщение было отредактировано: 8 июн 18, 18:25
8 июн 18, 16:09    [21479614]     Ответить | Цитировать Сообщить модератору
 Re: Дерево сборки  [new]
Посетитель
Member

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

+ и вообще
из экстрасексов и телепузиков я бы предпочел первое
8 июн 18, 16:11    [21479620]     Ответить | Цитировать Сообщить модератору
 Re: Дерево сборки  [new]
aleks222
Member

Откуда:
Сообщений: 926
Bob2012
aleks222,

Есть головное изделие, допустим ТРИМ.468369.003-01 и мы делаем запрос
select *
from ever
where ever_parim = 'ТРИМ.468369.003-01'
Получаем все изделия которые в него входят (здесь только часть)
ever_parim ever_lineno ever_ondate ever_detim
ТРИМ.468369.003-01 10 1899-12-31 00:00:00.000 ТРИМ.301412.022-01
ТРИМ.468369.003-01 20 1899-12-31 00:00:00.000 ТРИМ.301412.023
ТРИМ.468369.003-01 30 1899-12-31 00:00:00.000 ТРИМ.321455.013-02
ТРИМ.468369.003-01 40 1899-12-31 00:00:00.000 6Д6.832.046
ТРИМ.468369.003-01 50 1899-12-31 00:00:00.000 6В8.840.031-01
Теперь разворачиваем первое изделие которое в него входит
select *
from ever
where ever_parim ='ТРИМ.301412.022-01'
Получаем
ТРИМ.301412.022-01 10 1899-12-31 00:00:00.000 ТРИМ.687281.210
ТРИМ.301412.022-01 20 1899-12-31 00:00:00.000 ТРИМ.687281.211
ТРИМ.301412.022-01 30 1899-12-31 00:00:00.000 ТРИМ.687281.212
ТРИМ.301412.022-01 40 1899-12-31 00:00:00.000 ТРИМ.687281.213
Дальше
select *
from ever
where ever_parim ='ТРИМ.687281.210'
ТРИМ.687281.210 20 1899-12-31 00:00:00.000 6Д8.128.041-05
ТРИМ.687281.210 30 1899-12-31 00:00:00.000 2-0,8-2,5-7Л6307
ТРИМ.687281.210 40 1899-12-31 00:00:00.000 6212746747
ТРИМ.687281.210 50 1899-12-31 00:00:00.000 6349555475
select *
from ever
where ever_parim ='6Д8.128.041-05'
6Д8.128.041-05 10 1899-12-31 00:00:00.000 22000016000

И вот мы в конце
А хотелось бы сразу получить
ever_parim ever_lineno ever_ondate ever_detim
ТРИМ.468369.003-01 10 1899-12-31 00:00:00.000 ТРИМ.301412.022-01
ТРИМ.301412.022-01 10 1899-12-31 00:00:00.000 ТРИМ.687281.210
ТРИМ.687281.210 20 1899-12-31 00:00:00.000 6Д8.128.041-05
6Д8.128.041-05 10 1899-12-31 00:00:00.000 22000016000
ТРИМ.468369.003-01 20 1899-12-31 00:00:00.000 ТРИМ.301412.023 - (это следующее изделие из первого запроса)
и дальше разворачивать уже второе изделие, далее 3 и т.д.
Потом мы добавим критерии отбора и это сократит выборку, но это если получится сделать такой запрос


Дык, ты ж фсе сам написал.
Осталось создать временную таблицу и цикл.

ЗЫ. Ну еще рекурсивное СТЕ. Но временная таблица проще.
8 июн 18, 18:22    [21479957]     Ответить | Цитировать Сообщить модератору
 Re: Дерево сборки  [new]
aleks222
Member

Откуда:
Сообщений: 926
declare @BaseTable table( ever_parim nvarchar(64), ever_lineno int,  ever_ondate date, ever_detim nvarchar(64) );

declare @TempTable table( ever_parim nvarchar(64), ever_lineno int,  ever_ondate date, ever_detim nvarchar(64), lvl int );

declare @rc int, @lvl int = 0 

-- Есть головное изделие, допустим ТРИМ.468369.003-01 и мы делаем запрос
insert @TempTable select *, @lvl from @BaseTable where ever_parim = 'ТРИМ.468369.003-01';
set @rc = @@rowcount;

while @rc > 0 begin

   with t as ( select * from @TempTable where lvl = @lvl )
     insert @TempTable select b.*, @lvl + 1 from t inner join @BaseTable as b on t.ever_detim = b.ever_parim;
   set @rc = @@rowcount;

   set @lvl = @lvl + 1;

end;

select * from @TempTable;
8 июн 18, 18:34    [21479975]     Ответить | Цитировать Сообщить модератору
 Re: Дерево сборки  [new]
Bob2012
Member

Откуда:
Сообщений: 6
К сожалению, мои познания в SQL слишком незначительны и создание даже временной таблицы с циклом для меня проблема. Про рекурсивное СТЕ я вообще молчу. Всё что я находил по этой теме, для меня что китайские иероглифы.
8 июн 18, 18:37    [21479981]     Ответить | Цитировать Сообщить модератору
 Re: Дерево сборки  [new]
Bob2012
Member

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

Заранее благодарю. Проверю завтра, как на работе окажусь.
8 июн 18, 18:40    [21479984]     Ответить | Цитировать Сообщить модератору
 Re: Дерево сборки  [new]
Посетитель
Member

Откуда:
Сообщений: 1209
Bob2012,
А на рекурсии как то так

with a as
(select*from(values
 (1,2,10)
,(1,3,20)
,(2,4,10)
,(2,5,20)
,(3,6,10)
,(4,7,10)
,(4,8,20)
,(5,9,20)
,(6,10,20)
)a(pid,cid,ord))
,b as(
select pid,cid,cast(right('0000'+cast(row_number()over(partition by pid order by ord) as varchar(4)),4) as varchar(2000))ord2 
  from a
)
,c as
(select *
   from b
 union all
select b.pid,b.cid, cast(c.ord2 + b.ord2 as varchar(2000)) ord2
  from c
  join b
    on b.pid = c.cid
)
,d as(
select *,row_number()over(partition by pid,cid order by len(ord2) desc) r
  from c)
select pid,cid
  from d
 where r = 1
 order by ord2


pid и cid - это ваши parim|detim
ord - lineno

ну и решение имеет ряд ограничений:
1. уровень вложенности не более 100(решается через maxrecursion и при необходимости - замену varchcar(2000) на нечто большее)
2. не больше 9999 различных детей для одного родителя(тоже вопрос решаемый)
3. недопустимы циклические ссылки.
8 июн 18, 18:46    [21479988]     Ответить | Цитировать Сообщить модератору
 Re: Дерево сборки  [new]
Bob2012
Member

Откуда:
Сообщений: 6
Посетитель,

И Вам низкий поклон). Завтра всё обкатаю и отпишусь.
8 июн 18, 18:50    [21479993]     Ответить | Цитировать Сообщить модератору
 Re: Дерево сборки  [new]
Посетитель
Member

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

хммм, а вам что из предложенного нужно то?)
я делал упор на сортировку с учетом поиска в глубину
aleks222, похоже, решил что достаточно сортировки по поиску в ширину.
8 июн 18, 19:01    [21480014]     Ответить | Цитировать Сообщить модератору
 Re: Дерево сборки  [new]
Bob2012
Member

Откуда:
Сообщений: 6
Посетитель,

Завтра узнаю, когда с коллегой возьмёмся за эксперименты и расскажу. Мои знания не позволяют на данный момент понять всю глубину Вашей помощи, ибо я глуп в SQL)). Если по факту, то у нас есть код который вытягивает товарный план, но с нашими пожеланиями. Количество позиций около 2к, но около 70% это просто детали и они нас не интересуют. Вот остальные являются сборочными единицами и нам необходимо будет раскрыть примерно 500 позиций, вплоть до деталей. Глубина вряд ли будет больше 10-15, а ширина возможно может достигать 100, но это приблизительные цифры. Нужно будет проверять.
P.S. Если честно я пока даже не понимаю как применить Ваш цикл, а куда мне коды изделия добавлять)?. Надеюсь завтра разберёмся и всё получится.
8 июн 18, 19:23    [21480037]     Ответить | Цитировать Сообщить модератору
 Re: Дерево сборки  [new]
aleks222
Member

Откуда:
Сообщений: 926
Bob2012
Глубина вряд ли будет больше 10-15, а ширина возможно может достигать 100, но это приблизительные цифры. Нужно будет проверять.


Глубже и ширше!!!
Ээээ... стисняюсь спросить: ширина чего?

declare @BaseTable table( ever_parim nvarchar(64), ever_lineno int,  ever_ondate date, ever_detim nvarchar(64) );

insert @BaseTable
  values('Корпус', 10, '12.07.2018', 'механизм')
, ('Корпус', 20, '12.07.2018', 'переключатель')
, ('переключатель', 10, '12.07.2018', 'винт')
, ('переключатель', 20, '12.07.2018', 'гайка')
, ('переключатель', 30, '12.07.2018', 'шайба')
, ('переключатель', 40, '12.07.2018', 'кнопка')
, ('механизм', 10, '12.07.2018', 'колонка')
, ('механизм', 20, '12.07.2018', 'водило')
, ('механизм', 30, '12.07.2018', 'обойма')
, ('колонка', 10, '12.07.2018', 'Сталь 3')
;

declare @TempTable table( ever_parim nvarchar(64), ever_lineno int,  ever_ondate date, ever_detim nvarchar(64), lvl int );

declare @rc int, @lvl int = 0 

-- Есть головное изделие, допустим ТРИМ.468369.003-01 и мы делаем запрос
insert @TempTable select *, @lvl from @BaseTable where ever_parim = 'Корпус';
set @rc = @@rowcount;

while @rc > 0 begin

   with t as ( select * from @TempTable where lvl = @lvl )
     insert @TempTable select b.*, @lvl + 1 from t inner join @BaseTable as b on t.ever_detim = b.ever_parim;
   set @rc = @@rowcount;

   set @lvl = @lvl + 1;

end;

select * from @TempTable;


ever_parim	ever_lineno	ever_ondate	ever_detim	lvl
Корпус	10	2018-07-12	механизм	0
Корпус	20	2018-07-12	переключатель	0
переключатель	10	2018-07-12	винт	1
переключатель	20	2018-07-12	гайка	1
переключатель	30	2018-07-12	шайба	1
переключатель	40	2018-07-12	кнопка	1
механизм	10	2018-07-12	колонка	1
механизм	20	2018-07-12	водило	1
механизм	30	2018-07-12	обойма	1
колонка	10	2018-07-12	Сталь 3	2
9 июн 18, 11:25    [21481315]     Ответить | Цитировать Сообщить модератору
 Re: Дерево сборки  [new]
Посетитель
Member

Откуда:
Сообщений: 1209
aleks222
ширина чего?

графа же.
только не того, который Лев Николаевич.
а того, который должен получиться из данной номенклатуры изделий.

меня все еще мучает вопрос, а какую задачу решаем :)
судя по изначальному примеру - требуется таки именно пересортировать записи с учетом алгоритма поиска в глубину, ибо набор что есть/ что надо содержит абсолютно одинаковй набор данных, но в разном порядке.
9 июн 18, 11:40    [21481369]     Ответить | Цитировать Сообщить модератору
 Re: Дерево сборки  [new]
aleks222
Member

Откуда:
Сообщений: 926
Посетитель
aleks222
ширина чего?

графа же.
только не того, который Лев Николаевич.
а того, который должен получиться из данной номенклатуры изделий.

меня все еще мучает вопрос, а какую задачу решаем :)
судя по изначальному примеру - требуется таки именно пересортировать записи с учетом алгоритма поиска в глубину, ибо набор что есть/ что надо содержит абсолютно одинаковй набор данных, но в разном порядке.


"1 млн" чего-то там в таблице
и
" Количество позиций около 2к"

намекают на то, что выбираем таки не все.
просто пример неудачен.

Ну а сортировку... можно и сортировку.
9 июн 18, 11:50    [21481398]     Ответить | Цитировать Сообщить модератору
 Re: Дерево сборки  [new]
лолл
Member

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

Операция вставки слишком дорогостоящая, чтобы делать ее по одной записи в цикле... Есть же CTE
9 июн 18, 14:22    [21482011]     Ответить | Цитировать Сообщить модератору
 Re: Дерево сборки  [new]
aleks222
Member

Откуда:
Сообщений: 926
лолл
aleks222,

по одной записи в цикле... Есть же CTE


Ты точно арифметику освоил?
9 июн 18, 14:31    [21482048]     Ответить | Цитировать Сообщить модератору
 Re: Дерево сборки  [new]
лолл
Member

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

Леха, по одному уровню добавляешь.. CTE освоил?
9 июн 18, 16:03    [21482323]     Ответить | Цитировать Сообщить модератору
 Re: Дерево сборки  [new]
aleks222
Member

Откуда:
Сообщений: 926
лолл
aleks222,
Леха, по одному уровню добавляешь.. CTE освоил?

СТЕ животворящий не по одному?

В реальных задачах такого типа на больших объемах СТЕ сливает.
Ибо индексы в СТЕ не сделать.
9 июн 18, 16:39    [21482402]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить