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

Откуда:
Сообщений: 42
Есть таблица плана : Номенклатуру,дата,количество. Номенклатура может стоять в плане на несколько дат.
Номенклатура состоит из комплектующих(получаю рекурсивным запросом).
Нужно получить сколько комплектующих требуется на каждую дату.
Я циклом прохожу весь план и записываю комплектующие с датой во временную таблицу. Потом группирую по комплектующим и дате.
Как лучше решить эту задачу?
14 июн 18, 11:20    [21490374]     Ответить | Цитировать Сообщить модератору
 Re: Запрос много ко многим  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31364
Васелина
Как лучше решить эту задачу?
Рекурсивным CTE можно получить всё одним запросом.
14 июн 18, 11:31    [21490436]     Ответить | Цитировать Сообщить модератору
 Re: Запрос много ко многим  [new]
Васелина
Member

Откуда:
Сообщений: 42
alexeyvg, Где же здесь рекурсия? Рекурсивным сте я получаю список комплектующих на каждую номенклатура.
14 июн 18, 12:05    [21490580]     Ответить | Цитировать Сообщить модератору
 Re: Запрос много ко многим  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
Васелина,

With cte as
(Select Деталь, КолВо From План Where Дата Between @ДатаНач and @ДатаКон
Union all
Select b.Деталь, b.КолВо * a.КолВо --Количество сборок в плане * на количество деталей в сборке
 From cte a
 join Сборки b on a.Деталь = b.Сборка
)
Select Деталь,Sum(КолВо) as ОбщееКоличество 
 From CTE a
Where not exists (Select 1 From Сборки b Where a.Деталь = b.Сборка)
Group by Деталь
14 июн 18, 12:25    [21490641]     Ответить | Цитировать Сообщить модератору
 Re: Запрос много ко многим  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
Если по датам:

With cte as
(Select Деталь, Дата, КолВо From План Where Дата Between @ДатаНач and @ДатаКон
Union all
Select b.Деталь, a.Дата, b.КолВо * a.КолВо --Количество сборок в плане * на количество деталей в сборке
 From cte a
 join Сборки b on a.Деталь = b.Сборка
)
Select Деталь,Дата,Sum(КолВо) as ОбщееКоличество 
 From CTE a
Where not exists (Select 1 From Сборки b Where a.Деталь = b.Сборка)
Group by Деталь,Дата
14 июн 18, 12:28    [21490650]     Ответить | Цитировать Сообщить модератору
 Re: Запрос много ко многим  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31364
Васелина
alexeyvg, Где же здесь рекурсия? Рекурсивным сте я получаю список комплектующих на каждую номенклатура.
А, извиняюсь. невнимательно прочитал.

Так осталось самое простое - добавить в запрос в нужных местах SUM, COUNT и GROUP BY
14 июн 18, 13:06    [21490758]     Ответить | Цитировать Сообщить модератору
 Re: Запрос много ко многим  [new]
Васелина
Member

Откуда:
Сообщений: 42
Kopelly,
With cte as
(Select сборка, Дата, КолВо From План Where Дата Between @ДатаНач and @ДатаКон
Union all
Select b.Деталь, a.Дата, b.КолВо * a.КолВо --Количество сборок в плане * на количество деталей в сборке
From cte a
join Сборки b on a.Деталь = b.сборка)
Select Деталь,Дата,Sum(КолВо) as ОбщееКоличество
From CTE a
Where not exists (Select 1 From Сборки b Where a.Деталь = b.Сборка)
Group by Деталь,Дата

В плане стоят стоят сборки, а не детали.
Запрос зациклился.Рекурсия превышает 100.
14 июн 18, 13:26    [21490847]     Ответить | Цитировать Сообщить модератору
 Re: Запрос много ко многим  [new]
Васелина
Member

Откуда:
Сообщений: 42
Васелина, Если бы не было поля дата , то можно поставить SUm , group. b и связать две таблицы по изделию.
Но в таблице План Изделие повторяется несколько раз и в таблице с комплектующими изделие повторяется на каждую комплектующую. Количество комплектующих суммируется неправильно.
14 июн 18, 13:41    [21490903]     Ответить | Цитировать Сообщить модератору
 Re: Запрос много ко многим  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
Васелина,
Давай пример данных - советы будут предметнее.
15 июн 18, 05:43    [21492525]     Ответить | Цитировать Сообщить модератору
 Re: Запрос много ко многим  [new]
Васелина
Member

Откуда:
Сообщений: 42
Kopelly,
Задача стоит такая. Есть план на месяц. Который состоит из ежедневных планов. Номенклатура может повторяться .
Пример таблицы

изделие1 01-06-2018 5
изделие1 02-06-2018 3
изделие1 03-06-2018 1
изделие2 04-06-2018 4
изделие1 04-06-2018 5

Есть таблица иерархическая комплектующих и материалов изделий

Мне нужно на каждый день вытащить расход краски.

Я циклом обхожу таблицу план
вытаскиваю рекурсивным сте комплектующие по каждому изделию и выбираю краску и записываю данные во временную таблицу. Потом с ней работую. Можно ли эту задачу решить без цикла.
15 июн 18, 08:13    [21492622]     Ответить | Цитировать Сообщить модератору
 Re: Запрос много ко многим  [new]
Посетитель
Member

Откуда:
Сообщений: 1209
Васелина
Можно ли эту задачу решить без цикла.

можно.

Ваше рекурсивное cte совсем не обязательно применять к каждому изделию, отберите список изделий и рекурсивно начитайте по ним все нужные комплектующие.

более предметный ответ как уже неоднократно сказали, требует полного набора тестовых данных.
и код Ваш покажите, если не жалко :)
15 июн 18, 08:21    [21492630]     Ответить | Цитировать Сообщить модератору
 Re: Запрос много ко многим  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
Васелина
Kopelly,
Задача стоит такая. Есть план на месяц. Который состоит из ежедневных планов. Номенклатура может повторяться .

По идее 21490650 решает такую задачу.
Либо расписывай структуру всех таблиц (с наименованием и назначением полей), либо ищи ошибку в переносе логики под свои данные, либо просто выложи текст запроса который приводит к зацикливанию.
15 июн 18, 09:02    [21492698]     Ответить | Цитировать Сообщить модератору
 Re: Запрос много ко многим  [new]
Васелина
Member

Откуда:
Сообщений: 42
Kopelly,
with T(spvx,kodizd,kodsb,kodvx, kol,lv) AS
(SELECT spvx,s.kodizd,s.kodizd as kodsb,kodvx, kol,
1 AS lv
FROM sostav s ,(select kodizd from plano where dataz between '01-06-2018' and '30-06-2018' group by kodizd) as p
WHERE s.kodizd=p.kodizd
UNION ALL
SELECT s.spvx, s.kodizd,t.kodvx as kodsb,s.kodvx, s.kol*t.kol,
t.lv + 1 AS lv
FROM (sostav s inner join t on ( s.kodizd = t.kodvx ))
)
SELECT kodvx as Код, i1.izd as vxod,i2.izd ,lv ,kol
FROM ( T inner join izd i1 on (i1.Код=t.kodvx ) inner join izd i2 on i2.Код=t.kodsb )
where i1.izd='Краска'
order by lv
Сдесь группировка только по изделиям. Он не зацикливается . Но результат выдает неправильно. Увеличивает с строки изделие , комплектующие количество пропорционально количеству строк комплектующих в составе изделия.
15 июн 18, 11:35    [21493238]     Ответить | Цитировать Сообщить модератору
 Re: Запрос много ко многим  [new]
Васелина
Member

Откуда:
Сообщений: 42
Если я еще и дату добавлю в группировку то еще увеличивается пропорционально количеству строк по номенклатуре в плане.
15 июн 18, 11:37    [21493249]     Ответить | Цитировать Сообщить модератору
 Re: Запрос много ко многим  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
Васелина,
Если так?
with T(dataz,spvx,kodizd,kodsb,kodvx, kol,lv) AS 
(SELECT p.dataz,spvx,s.kodizd,s.kodizd as kodsb,kodvx, sum(p.kol*s.kol), 
 1 AS lv 
 FROM plano p 
 join sostav s on s.kodizd=p.kodizd
 where p.dataz between '01-06-2018' and '30-06-2018'
 Group by dataz,spvx,s.kodizd, kodvx
	UNION ALL 
 SELECT t.dataz,s.spvx, s.kodizd,t.kodvx as kodsb,s.kodvx, s.kol*t.kol, 
	t.lv + 1 AS lv 
 FROM sostav s 
 inner join t on  s.kodizd = t.kodvx
) 
SELECT t.dataz,t.kodvx as Код, i1.izd as vxod,i2.izd ,sum(t.kol)
FROM T 
inner join izd i1 on i1.Код=t.kodvx 
inner join izd i2 on i2.Код=t.kodsb
where i1.izd='Краска'
Group by t.dataz,t.kodvx,i1.izd,i2.izd 


Увеличивает с строки изделие , комплектующие количество пропорционально количеству строк комплектующих в составе изделия.

Логично если в каждом комплектующем изделия есть "Краска" - группирок то нет.
15 июн 18, 12:50    [21493485]     Ответить | Цитировать Сообщить модератору
 Re: Запрос много ко многим  [new]
Васелина
Member

Откуда:
Сообщений: 42
Kopelly,
Запрос работает неправильно ,Краска может входить в разные сборки , при этом сами сборки могут входить в разные сборки.
18 июн 18, 15:35    [21500202]     Ответить | Цитировать Сообщить модератору
 Re: Запрос много ко многим  [new]
НиколайСН
Member

Откуда:
Сообщений: 120
Что-то я не понял каким образом Номенклатура и Краска зависят друг от друга?

А чем обычная группировка не помогает?
Зачем вообще ТУТ Рекурсия и сборка данных на Номенклатуру?
Где Тут отношение М:М, если ты указал ТОЛЬКО одну таблицу?
18 июн 18, 16:14    [21500391]     Ответить | Цитировать Сообщить модератору
 Re: Запрос много ко многим  [new]
982183
Member

Откуда: VL
Сообщений: 3352
НиколайСН,

Видимо там многопередельное производство. (что подтверждается последним постом)
С достаточно стандартной схемой данных комплектов.
И автор не удосужилась всё это описать, уповая на телепатические способности коллег.
и обыденность ситуации.
19 июн 18, 03:31    [21501680]     Ответить | Цитировать Сообщить модератору
 Re: Запрос много ко многим  [new]
Васелина
Member

Откуда:
Сообщений: 42
982183, Вы правы. Хотелось бы узнать . Как правильнее реализовать механизм разузлования номенклатуры.
19 июн 18, 08:43    [21501868]     Ответить | Цитировать Сообщить модератору
 Re: Запрос много ко многим  [new]
982183
Member

Откуда: VL
Сообщений: 3352
Последовательным разукомплектованием.
Другого способа нет.

если бы вы привели тестовый пример комплектов
(например здесь - http://sqlfiddle.com/ )
и в кратко описали структуру данных,
то можно было бы говорить более предметно.
19 июн 18, 08:54    [21501895]     Ответить | Цитировать Сообщить модератору
 Re: Запрос много ко многим  [new]
982183
Member

Откуда: VL
Сообщений: 3352
Тем более, что возможна ошибка в описании комплектов, при которой возникает зацикливание.
19 июн 18, 09:22    [21502004]     Ответить | Цитировать Сообщить модератору
 Re: Запрос много ко многим  [new]
НиколайСН
Member

Откуда:
Сообщений: 120
Васелина
Kopelly,
Задача стоит такая. Есть план на месяц. Который состоит из ежедневных планов. Номенклатура может повторяться .
Пример таблицы

изделие1 01-06-2018 5
изделие1 02-06-2018 3
изделие1 03-06-2018 1
изделие2 04-06-2018 4
изделие1 04-06-2018 5

Есть таблица иерархическая комплектующих и материалов изделий

Мне нужно на каждый день вытащить расход краски.

Я циклом обхожу таблицу план
вытаскиваю рекурсивным сте комплектующие по каждому изделию и выбираю краску и записываю данные во временную таблицу. Потом с ней работую. Можно ли эту задачу решить без цикла.


Я так понял что имеется всё-таки 2 таблицы, где одна из них имеет тип фактовой (значения по расходу краски) и Нормативно справочную (таблица иерархическая комплектующих и материалов изделий).

Тем более тогда не понимаю зачем тебе рекурсия, тем более это делается скорее всего одним запросом по связке [KEY] и [PARENT_KEY], ибо я увидел только 2 уровня НСИ: Изделие и Комплектующие.

Если тебе надо посчитать количества комплектующих (краски) за определенную дату, то это делается SUM() с банальным Джоином 2-х этих таблиц и группировкой (GROUP BY) по дате.

Или же я не понимаю?

В идеале конечно же хотелось бы более правильно поставленную задачу, не уповая на телепатию архитектуры Вашей БД, где описано: ДАНО и ВОПРОС
19 июн 18, 11:57    [21502606]     Ответить | Цитировать Сообщить модератору
 Re: Запрос много ко многим  [new]
982183
Member

Откуда: VL
Сообщений: 3352
НиколайСН,

У неё многопередельное производство.
Пельмени=тесто+фарш
Тесто=мука+яйца
Фарш=мясо+сало+соя
19 июн 18, 12:04    [21502627]     Ответить | Цитировать Сообщить модератору
 Re: Запрос много ко многим  [new]
Васелина
Member

Откуда:
Сообщений: 42
Как найти краску я знаю. Нахожу все изделия рекурсивным запросом в которые входит краска и стыкую с планом изделий на месяц по датам, получается запрос один ко многим. А как разузловать всю номенклатуру по всем комплекутющим и материалам без цикла я не знаю.
19 июн 18, 13:13    [21502946]     Ответить | Цитировать Сообщить модератору
 Re: Запрос много ко многим  [new]
НиколайСН
Member

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

Ну если так, то ДА, рекурсией собирается элементарно - выше примеры.

Ну а дальше кто мешает выполнить GROUP BY и сделать SUM обычным запросом?
типа:
SELECT 
    SUM(Значение), 
    Краска, 
    Дата 
WHERE Деталь = 'Краска' 
GROUP BY 
    Краска, 
    Дата 

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

Да собстна, уже есть примеры выше, осталось мизер допилить и готово.
19 июн 18, 13:14    [21502953]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить