Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Дерево сборки / 23 сообщений из 23, страница 1 из 1
08.06.2018, 14:57
    #39658415
Bob2012
Bob2012
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дерево сборки
Добрый день. Для работы требуется разворачивать блоки, в которые входят сборки, а в них подсборки и т.д. Вот только возникла проблема, что данный уровень запроса мне не по зубам. Сейчас это делается через спец. функцию, но она одноуровневая и решение данной задачи занимает несколько дней. Надеюсь на Вашу помощь.
Требуемая информация хранится в одной таблице ever. В ней десятки столбцов и около 1 млн. строк, но требуемые данные находятся в 2 столбцах: ever_parim и ever_detim
Для примера. Что имеем:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
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

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

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
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	| кнопка
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2018, 15:34
    #39658454
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дерево сборки
Экстрасексы и телепузики последнее время совсем повывелись.
Придется те напрячься объяснить.

Каким образом из первого следует второе?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2018, 15:38
    #39658457
Посетитель
Посетитель
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дерево сборки
aleks222,

похоже на обход дерева в глубину
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2018, 15:53
    #39658464
buser
buser
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дерево сборки
Посетительaleks222,
похоже на обход дерева в глубину
А дерево-то где?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2018, 15:54
    #39658465
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дерево сборки
Посетительaleks222,

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

Так, телепузики подтянулись!
Ты не скромничай - ты все выкладывай. Как есть.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2018, 16:07
    #39658472
Посетитель
Посетитель
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дерево сборки
aleks222,

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

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

строка - ребро, ever_parim,ever_detim - пары вершин.
обход в порядке lineno начинай с узла, который есть только в parim
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2018, 16:09
    #39658474
Bob2012
Bob2012
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дерево сборки
aleks222,

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


Получаем все изделия которые в него входят (здесь только часть)
Код: plaintext
1.
2.
3.
4.
5.
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

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


Получаем
Код: plaintext
1.
2.
3.
ТРИМ.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

Дальше
Код: sql
1.
2.
3.
select *
from ever
where ever_parim ='ТРИМ.687281.210'


Код: plaintext
1.
2.
3.
ТРИМ.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

Код: sql
1.
2.
3.
select *
from ever
where ever_parim ='6Д8.128.041-05'


Код: plaintext
6Д8.128.041-05        	10	1899-12-31 00:00:00.000	22000016000


И вот мы в конце
А хотелось бы сразу получить
Код: plaintext
1.
2.
3.
4.
5.
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 и т.д.
Потом мы добавим критерии отбора и это сократит выборку, но это если получится сделать такой запрос
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2018, 16:11
    #39658476
Посетитель
Посетитель
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дерево сборки
aleks222 ,

 
и вообще из экстрасексов и телепузиков я бы предпочел первое
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2018, 18:22
    #39658548
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дерево сборки
Bob2012aleks222,

Есть головное изделие, допустим ТРИМ.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 и т.д.
Потом мы добавим критерии отбора и это сократит выборку, но это если получится сделать такой запрос

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

ЗЫ. Ну еще рекурсивное СТЕ. Но временная таблица проще.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2018, 18:34
    #39658551
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дерево сборки
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
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;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2018, 18:37
    #39658554
Bob2012
Bob2012
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дерево сборки
К сожалению, мои познания в SQL слишком незначительны и создание даже временной таблицы с циклом для меня проблема. Про рекурсивное СТЕ я вообще молчу. Всё что я находил по этой теме, для меня что китайские иероглифы.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2018, 18:40
    #39658555
Bob2012
Bob2012
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дерево сборки
aleks222,

Заранее благодарю. Проверю завтра, как на работе окажусь.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2018, 18:46
    #39658557
Посетитель
Посетитель
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дерево сборки
Bob2012,
А на рекурсии как то так

Код: 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.
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. недопустимы циклические ссылки.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2018, 18:50
    #39658561
Bob2012
Bob2012
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дерево сборки
Посетитель,

И Вам низкий поклон). Завтра всё обкатаю и отпишусь.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2018, 19:01
    #39658568
Посетитель
Посетитель
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дерево сборки
Bob2012,

хммм, а вам что из предложенного нужно то?)
я делал упор на сортировку с учетом поиска в глубину
aleks222, похоже, решил что достаточно сортировки по поиску в ширину.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2018, 19:23
    #39658576
Bob2012
Bob2012
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дерево сборки
Посетитель,

Завтра узнаю, когда с коллегой возьмёмся за эксперименты и расскажу. Мои знания не позволяют на данный момент понять всю глубину Вашей помощи, ибо я глуп в SQL)). Если по факту, то у нас есть код который вытягивает товарный план, но с нашими пожеланиями. Количество позиций около 2к, но около 70% это просто детали и они нас не интересуют. Вот остальные являются сборочными единицами и нам необходимо будет раскрыть примерно 500 позиций, вплоть до деталей. Глубина вряд ли будет больше 10-15, а ширина возможно может достигать 100, но это приблизительные цифры. Нужно будет проверять.
P.S. Если честно я пока даже не понимаю как применить Ваш цикл, а куда мне коды изделия добавлять)?. Надеюсь завтра разберёмся и всё получится.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.06.2018, 11:25
    #39658782
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дерево сборки
Bob2012 Глубина вряд ли будет больше 10-15, а ширина возможно может достигать 100, но это приблизительные цифры. Нужно будет проверять.

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

Код: 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.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.06.2018, 11:40
    #39658789
Посетитель
Посетитель
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дерево сборки
aleks222ширина чего?
графа же.
только не того, который Лев Николаевич.
а того, который должен получиться из данной номенклатуры изделий.

меня все еще мучает вопрос, а какую задачу решаем :)
судя по изначальному примеру - требуется таки именно пересортировать записи с учетом алгоритма поиска в глубину, ибо набор что есть/ что надо содержит абсолютно одинаковй набор данных, но в разном порядке.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.06.2018, 11:50
    #39658793
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дерево сборки
Посетительaleks222ширина чего?
графа же.
только не того, который Лев Николаевич.
а того, который должен получиться из данной номенклатуры изделий.

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

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

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

Ну а сортировку... можно и сортировку.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.06.2018, 14:22
    #39658896
лолл
лолл
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дерево сборки
aleks222,

Операция вставки слишком дорогостоящая, чтобы делать ее по одной записи в цикле... Есть же CTE
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.06.2018, 14:31
    #39658906
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дерево сборки
лоллaleks222,

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

Ты точно арифметику освоил?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.06.2018, 16:03
    #39658964
лолл
лолл
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дерево сборки
aleks222,

Леха, по одному уровню добавляешь.. CTE освоил?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.06.2018, 16:39
    #39658987
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дерево сборки
лоллaleks222,
Леха, по одному уровню добавляешь.. CTE освоил?
СТЕ животворящий не по одному?

В реальных задачах такого типа на больших объемах СТЕ сливает.
Ибо индексы в СТЕ не сделать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Дерево сборки / 23 сообщений из 23, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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