Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
Draginsv Member Откуда: Сообщений: 457 |
Доброе утро! Пытаюсь сочинить запрос следующей задачи : исключить из товарооборота внутренние манипуляции по складу Имеется [товар] [от кого] [кому] 1 12 10 1 10 20 1 20 101 2 12 10 2 10 114 3 12 10 3 10 NULL В отчете должно быть [товар] [от кого] [кому] 1 12 101 2 12 114 3 12 NULL т.е. нужно исключить 10 и 20 конрагентов (подразделения склада) оставить только внешних group by по товару и далее туплю Заранее спасибо! |
21 ноя 13, 07:32 [15165858] Ответить | Цитировать Сообщить модератору |
Draginsv Member Откуда: Сообщений: 457 |
Может быть и так Имеется [товар] [от кого] [кому] 1 12 10 1 10 20 1 20 101 1 101 10 1 10 NULL 2 12 10 2 10 114 3 12 10 3 10 NULL В отчете должно быть [товар] [от кого] [кому] 1 12 101 1 101 NULL 2 12 114 3 12 NULL Все телодвижения отсортированы по дате события |
21 ноя 13, 07:40 [15165863] Ответить | Цитировать Сообщить модератору |
Ruuu Member Откуда: Иркутск Сообщений: 4272 |
WHERE [кому] not in (10,20) Рекомендации по оформлению сообщений в форуме |
||
21 ноя 13, 07:45 [15165869] Ответить | Цитировать Сообщить модератору |
Jaffar Member Откуда: Сообщений: 633 |
Draginsv,declare @__TEMP table(ID int identity(1, 1) primary key, IDProduct int, StoreFrom int, StoreTo int) insert @__TEMP(IDProduct, StoreFrom, StoreTo) select 1, 12, 10 union all select 1, 10, 20 union all select 1, 20, 101 union all select 2, 12, 10 union all select 2, 10, 114 union all select 3, 12, 10 union all select 3, 10, NULL --select * from @__TEMP select t.IDProduct, t1.StoreFrom, t2.StoreTo from (select IDProduct, min(ID) ID_min, max(ID) ID_max from @__TEMP group by IDProduct) t join @__TEMP t1 on t1.IDProduct = t.IDProduct and t1.ID = t.ID_min join @__TEMP t2 on t2.IDProduct = t.IDProduct and t2.ID = t.ID_max where t1.StoreFrom not in (10, 20) or t1.StoreFrom not in (10, 20) --если условия в реальности хитрее чем в примере то можни использовать cross apply select t.IDProduct, t1.StoreFrom, t2.StoreTo from (select distinct IDProduct from @__TEMP) t cross apply(select top 1 t1.StoreFrom from @__TEMP t1 where t1.IDProduct = t.IDProduct order by t1.ID asc) t1 cross apply(select top 1 t2.StoreTo from @__TEMP t2 where t2.IDProduct = t.IDProduct order by t2.ID desc) t2 where t1.StoreFrom not in (10, 20) or t1.StoreFrom not in (10, 20) НО - в реале так делать нельзя. Только если есть однозначные сведения что именно этот экз. товара двигается по складу. |
21 ноя 13, 08:13 [15165914] Ответить | Цитировать Сообщить модератору |
Jaffar Member Откуда: Сообщений: 633 |
Jaffar, т.е. по цепочке перемещений для каждого продукта вы должны найти из них начальную и конечную точки, а потом проверить что хотябы одна из них не является внутренней, так? |
21 ноя 13, 08:17 [15165923] Ответить | Цитировать Сообщить модератору |
Draginsv Member Откуда: Сообщений: 457 |
да правильно, но как min и max здесь сработают если есть внутри цепочки внешние контрагенты. min и max берут только крайние точки, а промежуточные игнорируют. вот поэтому я и туплю. |
21 ноя 13, 08:41 [15165986] Ответить | Цитировать Сообщить модератору |
Jaffar Member Откуда: Сообщений: 633 |
т.е. насколько под_цепочек распадутся все перемещения 1 продукта заранее не известно, так? т.е. нужно определиться что является условием начала и конца для под_цепочки. определитесь с условиями - тогда напишу вам по ним запрос. а то так данных мало. без курсора можно функцию табличную сделать которая будет по ID продукта возвращать все его под_цепочки. а снаружи просто проверяете что in или out - не внутр. если у вас есть решение с курсором - выложите его, попробуем преобразовать к без_курсорному виду. |
||
21 ноя 13, 09:02 [15166073] Ответить | Цитировать Сообщить модератору |
Draginsv Member Откуда: Сообщений: 457 |
в том то и дело что кол-во под_цепочек не ограничено. а в курсоре ход рассуждений такой создать курсор по товарам с внутренними контрагентами, цикл по нему, внутри цикла: создать курсор по конкретному товару с сортировкой по дате, цикл, внутри. запомнить кому в переменную переход к следующей записи, если переменная кому совпадает с полем от кого в записи если поле кому - внутренний помечаем на удаление эту запись иначе апдейтим запись соотв. переменной кому в поле кому прописываем значение поля кому текущей записи конец если конец если переходим к следующей примерно так |
21 ноя 13, 09:34 [15166214] Ответить | Цитировать Сообщить модератору |
Jaffar Member Откуда: Сообщений: 633 |
Draginsv, а sql код есть? |
21 ноя 13, 09:41 [15166261] Ответить | Цитировать Сообщить модератору |
aleks2
Guest |
Вот, страдалец, твой коллега https://www.sql.ru/forum/1060429/pomoshh-s-zapros |
21 ноя 13, 09:47 [15166294] Ответить | Цитировать Сообщить модератору |
Draginsv Member Откуда: Сообщений: 457 |
кажется можно попробовать так если рассматривать отдельный товар во временную таблицу добавить записи где от кого - внешние контрагенты апдейтить во временной поле кому позапросом - найти первую запись в исходной таблице по данному товару с полем кому - внешние контрагенты с датой отправки кому больше даты в записи во временной таблице для поля от кого. Но это мне кажется примитив и он может дать сбой при каких нибудь хитростях с внутренними контрагентами. здесь нет анализа на неразрывность перемещения. |
21 ноя 13, 10:06 [15166385] Ответить | Цитировать Сообщить модератору |
Draginsv Member Откуда: Сообщений: 457 |
Jaffar, все пока в мыслях |
21 ноя 13, 10:09 [15166398] Ответить | Цитировать Сообщить модератору |
Алексей Куренков Member [заблокирован] Откуда: Москва Сообщений: 567 |
Draginsv, В Вашей структуре нет точного определения какая операция первая, какая последняя. Т.е. если мы увидим такое: declare @tbl table ( товар int, отКого int, кому int ) insert @tbl values (1, 12, 10 ) ,(1, 10, 20 ) ,(1, 20, 101 ) ,(2, 12, 10 ) ,(2, 10, 114 ) ,(3, 12, 10 ) ,(3, 10, NULL) ,(4, 15, 16) ,(4, 16, 15) И попытаемся определить от кого к кому пришел 4й товар, то можно интерпретировать что от 15->16->15 = 15, 15. А можно и 16-16. Не ясно какая первая какая последняя операция. Нужно вводить поле дата операции или порядковый номер операции. |
21 ноя 13, 10:48 [15166653] Ответить | Цитировать Сообщить модератору |
Алексей Куренков Member [заблокирован] Откуда: Москва Сообщений: 567 |
Draginsv, Ну и если ввести дату операции товара, на первый взгляд без оптимизаций как вариант такой запрос: declare @tbl table ( товар int, отКого int, кому int, дата datetime ) insert @tbl values (1, 12, 10 ,'2013-01-01') ,(1, 10, 20 ,'2013-01-02') ,(1, 20, 101 ,'2013-01-03') ,(2, 12, 10 ,'2013-01-04') ,(2, 10, 114 ,'2013-01-05') ,(3, 12, 10 ,'2013-01-06') ,(3, 10, NULL ,'2013-01-07') ,(4, 15, 16 ,'2013-01-08') ,(4, 16, 15 ,'2013-01-09') ; with c as ( select * ,row_number() over (partition by товар order by дата asc) as номер1 ,row_number() over (partition by товар order by дата desc) as номер2 from @tbl ) select c1.товар, c1.отКого, c2.кому from c as c1 join c c2 on c1.товар = c2.товар and c1.номер1=1 and c2.номер2=1 |
21 ноя 13, 10:54 [15166687] Ответить | Цитировать Сообщить модератору |
Jaffar Member Откуда: Сообщений: 633 |
и не только . ибо с 1 --> 2 может прийти 5 кг муки а с 2 --> 3 может прийти 2 кг муки или 7 кг муки. как это понимать |
||
21 ноя 13, 11:27 [15166923] Ответить | Цитировать Сообщить модератору |
Draginsv Member Откуда: Сообщений: 457 |
товар уникальный с серийным номером. поле дата - по нему сортировка а как я предложил - оно в подзапросе. Алексей, буду пытаться понять Вашу мысль. |
21 ноя 13, 11:48 [15167112] Ответить | Цитировать Сообщить модератору |
Draginsv Member Откуда: Сообщений: 457 |
Алексей Для такого набора данных (1, 12, 10 ,'2013-01-01') ,(1, 10, 20 ,'2013-01-02') ,(1, 20, 101 ,'2013-01-03') ,(1, 101, 10 ,'2013-01-03 1:00') ,(1, 10, NULL ,'2013-01-03 2:00') ,(2, 12, 10 ,'2013-01-04') ,(2, 10, 114 ,'2013-01-05') ,(3, 12, 10 ,'2013-01-06') ,(3, 10, NULL ,'2013-01-07') ,(4, 15, 16 ,'2013-01-08') ,(4, 16, 15 ,'2013-01-09') не работает должно получиться 1 12 101 1 101 NULL 2 12 114 3 12 NULL |
21 ноя 13, 11:55 [15167152] Ответить | Цитировать Сообщить модератору |
Алексей Куренков Member [заблокирован] Откуда: Москва Сообщений: 567 |
Немного странно... видимо я не все понял? задачу я понимаю так, что если рассматривать 1й товар то цепочка идет: 12->10->20->101->10->null Еще проще без проверки целостности всей цепочки - первый владелец товара и последний. что в запросе и сделано. по запросу получается 12->null каким образом у Вас дважды в результате товар с кодом 1 получается - это для меня или загадка или я не понял каким образом должно получиться.. каким образом должно получиться: 1 12 101 1 101 NULL ? |
||
21 ноя 13, 12:05 [15167235] Ответить | Цитировать Сообщить модератору |
Гость333 Member Откуда: Сообщений: 3683 |
ТС писал, что "нужно исключить 10 и 20 конрагентов". Исключив их из цепочки, получим такое: 12->101->null. Разложив звенья цепочки, получаем пары (12, 101) и (101, Null). |
||
21 ноя 13, 12:09 [15167276] Ответить | Цитировать Сообщить модератору |
Jaffar Member Откуда: Сообщений: 633 |
ну вот как-то наверное так:declare @__TEMP table(ID int identity(1, 1) primary key, IDProduct int, StoreFrom int, StoreTo int) insert @__TEMP(IDProduct, StoreFrom, StoreTo) select 1, 12, 10 union all select 1, 10, 20 union all select 1, 20, 101 union all select 1, 101, 102 union all select 1, 102, 103 union all select 1, 103, 101 union all select 1, 101, 10 union all select 1, 10, NULL union all select 2, 12, 10 union all select 2, 10, 114 union all select 3, 12, 10 union all select 3, 10, NULL select t3.IDProduct, row_number() over(partition by t3.IDProduct order by t3.NNX asc) NNX_2, NULLIF(t3.Stores, -1) StoresFrom, NULLIF(t3.StoresNext, -1)StoresTo from (select t.IDProduct, t2.Stores, row_number() over(partition by t.IDProduct order by t.ID asc, t2.NN asc) NNX, LEAD(t2.Stores, 1) over(partition by t.IDProduct order by t.ID asc, t2.NN asc) StoresNext from @__TEMP t --- выстраиваем их в цепочку cross apply(select 1 NN, IsNULL(t.StoreFrom, -1) Stores where IsNULL(t.StoreFrom, 0) not in (10, 20) union all select 2 NN, ISNULL(t.StoreTo, -1) where IsNULL(t.StoreTo, 0) not in (10, 20) /**/ ) t2 /**/ ) t3 where t3.Stores != t3.StoresNext -- убираем дубли order by t3.IDProduct, NNX_2 |
21 ноя 13, 14:40 [15168514] Ответить | Цитировать Сообщить модератору |
Draginsv Member Откуда: Сообщений: 457 |
Спасибо! как сложно. будем осмысливать. |
21 ноя 13, 14:45 [15168562] Ответить | Цитировать Сообщить модератору |
Zandr Member Откуда: Москва Сообщений: 576 |
Можно как-то так попробовать:WITH Res (IDProduct, StoreFrom, StoreTo, _Level) AS (SELECT t.IDProduct, t.StoreFrom, t.StoreTo, 1 _Level FROM TestTable t WHERE NOT EXISTS (SELECT 1 FROM TestTable WHERE IDProduct = t.IDProduct AND StoreTo = t.StoreFrom) UNION ALL SELECT t.IDProduct, t.StoreFrom, t.StoreTo, r._Level + 1 FROM Res r JOIN TestTable t ON t.IDProduct = r.IDProduct AND t.StoreFrom = r.StoreTo ) SELECT r.IDProduct, r.StoreFrom, r1.StoreTo FROM Res r JOIN Res r1 ON r1.IDProduct = r.IDProduct AND r1._Level = (SELECT MAX(_Level) FROM Res WHERE IDProduct = r.IDProduct) WHERE r._Level = 1 ORDER BY r.IDProduct; (Для второго случая, где был возврат к тому же айдишнику надо будет доделать, ориентируясь на дату или какие-то еще поля) |
21 ноя 13, 18:29 [15170282] Ответить | Цитировать Сообщить модератору |
Zandr Member Откуда: Москва Сообщений: 576 |
Zandr, А блин... про 10 и 20, как разделители, тоже не понял сразу... |
21 ноя 13, 19:32 [15170499] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |