Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
butthead
Guest |
Продано 15 граблей и 5 лопат из пяти партий. 6 граблей и одна лопата волевым решением руководства были отнесены к неликвидам. Как вывести данные о продажах и неликвидах в разрезе партий, считая неликвидными партии с более ранним приходом? create table sales (good char(10),deldate date, qty int, cost money,price money); insert sales values ('rake', '01/01/01',5,100,110),('rake', '02/01/01',3,105,110),('rake', '03/01/01',7,102,110), ('spade', '04/01/01',2,10,15),('spade', '05/01/01',3,12,15); create table surplus (good char(10), qty int); insert surplus values ('rake',6),('spade',1); sales good deldate qty cost price rake 2001-01-01 5 100,00 110,00 rake 2001-02-01 3 105,00 110,00 rake 2001-03-01 7 102,00 110,00 spade 2001-04-01 2 10,00 15,00 spade 2001-05-01 3 12,00 15,00 surplus good qty rake 6 spade 1 В результате должно получиться: good deldate qty cost price is_surplus rake 2001-01-01 5 100,00 110,00 1 rake 2001-02-01 1 105,00 110,00 1 rake 2001-02-01 2 105,00 110,00 0 rake 2001-03-01 7 102,00 110,00 0 spade 2001-04-01 1 10,00 15,00 1 spade 2001-04-01 1 10,00 15,00 0 spade 2001-05-01 3 12,00 15,00 0 |
14 май 13, 13:49 [14292589] Ответить | Цитировать Сообщить модератору |
hoha_ftf Member Откуда: ЕКБ Сообщений: 42 |
butthead,if object_id('tempdb..#t') is not null drop table #t select s1.* , isnull( sum( s2.qty ), 0 ) + 1 as begQ , isnull( sum( s2.qty ), 0 ) + s1.qty as endQ into #t from sales s1 left join sales s2 on s1.good = s2.good and s1.deldate > s2.deldate group by s1.good, s1.deldate, s1.qty, s1.cost, s1.price select t.good, t.deldate, t.qty, t.cost, t.price, 1 as is_surplus from #t t inner join surplus s on t.good = s.good where s.qty >= t.endQ union all select t.good, t.deldate, t.qty, t.cost, t.price, 0 as is_surplus from #t t inner join surplus s on t.good = s.good where s.qty < t.begQ union all select t.good, t.deldate, s.qty - t.begQ + 1, t.cost, t.price, 1 as is_surplus from #t t inner join surplus s on t.good = s.good where s.qty >= t.begQ and s.qty < t.endQ union all select t.good, t.deldate, t.endQ - s.qty, t.cost, t.price, 0 as is_surplus from #t t inner join surplus s on t.good = s.good where s.qty >= t.begQ and s.qty < t.endQ order by good, deldate, is_surplus desc |
14 май 13, 15:00 [14293246] Ответить | Цитировать Сообщить модератору |
butthead
Guest |
hoha_ftf, Спасибо огромное! Но это как-то слишком... |
14 май 13, 15:05 [14293283] Ответить | Цитировать Сообщить модератору |
Remind Member Откуда: UK Сообщений: 523 |
Слишком это торговать неликвидом :) |
14 май 13, 17:48 [14294692] Ответить | Цитировать Сообщить модератору |
butthead
Guest |
Remind, Слишком - это работать продавцом в компании, где система мотивации меняется ежемесячно и бонусы, и немаленькие штрафы зависят от "волевых решений" задним числом.. Но не все герои еще разбежались.. Посчитать зп по бумажке манагеры давно не могут, многие параметры берутся без возможности проверки по данным, выданным системой. И для программистов лучше, чтобы система считала правильно, дабы не быть побитыми.. |
14 май 13, 18:17 [14294923] Ответить | Цитировать Сообщить модератору |
butthead
Guest |
А через табличную переменную хуже будет? Может через CTE эффективнее можно? Это будет или часть составного запроса, но вероятно лучше будет вынести как отдельный view. В табличке sales около миллиона строк, surplus пара тысяч строк. declare @t table(good char(10),deldate date, qty int, cost money,price money, begQ int,endQ int) insert @t select s1.* , isnull( sum( s2.qty ), 0 ) + 1 as begQ , isnull( sum( s2.qty ), 0 ) + s1.qty as endQ from sales s1 left join sales s2 on s1.good = s2.good and s1.deldate > s2.deldate group by s1.good, s1.deldate, s1.qty, s1.cost, s1.price select t.good, t.deldate, t.qty, t.cost, t.price, 1 as is_surplus from @t t inner join surplus s on t.good = s.good where s.qty >= t.endQ union all select t.good, t.deldate, t.qty, t.cost, t.price, 0 as is_surplus from @t t inner join surplus s on t.good = s.good where s.qty < t.begQ union all select t.good, t.deldate, s.qty - t.begQ + 1, t.cost, t.price, 1 as is_surplus from @t t inner join surplus s on t.good = s.good where s.qty >= t.begQ and s.qty < t.endQ union all select t.good, t.deldate, t.endQ - s.qty, t.cost, t.price, 0 as is_surplus from @t t inner join surplus s on t.good = s.good where s.qty >= t.begQ and s.qty < t.endQ order by good, deldate, is_surplus desc |
14 май 13, 22:37 [14295911] Ответить | Цитировать Сообщить модератору |
hoha_ftf Member Откуда: ЕКБ Сообщений: 42 |
А вообще самое лучшее (для легкости запросов) - хранить begQ и endQ в таблице Sales. Один раз посчитать для существующих данных, а для новых считать при вставке. |
15 май 13, 08:13 [14296709] Ответить | Цитировать Сообщить модератору |
hoha_ftf Member Откуда: ЕКБ Сообщений: 42 |
Точнее даже одно поле begQ |
15 май 13, 08:15 [14296714] Ответить | Цитировать Сообщить модератору |
butthead
Guest |
Инсерты убрал, стало еще страшнее.. Хочу попробовать обойтись пока без добавления полей в таблицу. select t.good,t.deldate,ss.qty,1 as is_surplus from (select s1.good,s1.deldate,isnull( sum( s2.qty ),0 ) + 1 as begQ,isnull( sum( s2.qty ),0 ) + s1.qty as endQ from sales s1 left join sales s2 on s1.good = s2.good and s1.deldate > s2.deldate group by s1.good,s1.deldate,s1.qty) t,surplus s,sales ss where t.good = s.good and s.qty >= t.endQ and ss.good=t.good and ss.deldate=t.deldate union all select t.good,t.deldate,ss.qty,0 as is_surplus from (select s1.good,s1.deldate,isnull( sum( s2.qty ),0 ) + 1 as begQ,isnull( sum( s2.qty ),0 ) + s1.qty as endQ from sales s1 left join sales s2 on s1.good = s2.good and s1.deldate > s2.deldate group by s1.good,s1.deldate,s1.qty) t,surplus s,sales ss where t.good = s.good and s.qty < t.begQ and ss.good=t.good and ss.deldate=t.deldate union all select t.good,t.deldate,s.qty - t.begQ + 1,1 as is_surplus from (select s1.good,s1.deldate,isnull( sum( s2.qty ),0 ) + 1 as begQ,isnull( sum( s2.qty ),0 ) + s1.qty as endQ from sales s1 left join sales s2 on s1.good = s2.good and s1.deldate > s2.deldate group by s1.good,s1.deldate,s1.qty) t,surplus s where s.qty >= t.begQ and s.qty < t.endQ and t.good = s.good union all select t.good,t.deldate,t.endQ - s.qty,0 as is_surplus from (select s1.good,s1.deldate,isnull( sum( s2.qty ),0 ) + 1 as begQ,isnull( sum( s2.qty ),0 ) + s1.qty as endQ from sales s1 left join sales s2 on s1.good = s2.good and s1.deldate > s2.deldate group by s1.good,s1.deldate,s1.qty) t,surplus s where s.qty >= t.begQ and s.qty < t.endQ and t.good = s.good order by good,deldate,is_surplus desc |
15 май 13, 12:55 [14298542] Ответить | Цитировать Сообщить модератору |
butthead
Guest |
через withwith t(good,deldate,begQ,endQ)as (select s1.good,s1.deldate,isnull( sum( s2.qty ),0 ) + 1 as begQ,isnull( sum( s2.qty ),0 ) + s1.qty as endQ from sales s1 left join sales s2 on s1.good = s2.good and s1.deldate > s2.deldate group by s1.good,s1.deldate,s1.qty) select t.good,t.deldate,ss.qty,1 as is_surplus from t,surplus s,sales ss where t.good = s.good and s.qty >= t.endQ and ss.good=t.good and ss.deldate=t.deldate union all select t.good,t.deldate,ss.qty,0 as is_surplus from t,surplus s,sales ss where t.good = s.good and s.qty < t.begQ and ss.good=t.good and ss.deldate=t.deldate union all select t.good,t.deldate,s.qty - t.begQ + 1,1 as is_surplus from t,surplus s where s.qty >= t.begQ and s.qty < t.endQ and t.good = s.good union all select t.good,t.deldate,t.endQ - s.qty,0 as is_surplus from t,surplus s where s.qty >= t.begQ and s.qty < t.endQ and t.good = s.good order by good,deldate,is_surplus desc |
15 май 13, 13:27 [14298834] Ответить | Цитировать Сообщить модератору |
aleks2
Guest |
И ты мечтаешь, шоб (select s1.good,s1.deldate,isnull( sum( s2.qty ),0 ) + 1 as begQ,isnull( sum( s2.qty ),0 ) + s1.qty as endQ from sales s1 left join sales s2 on s1.good = s2.good and s1.deldate > s2.deldate group by s1.good,s1.deldate,s1.qty) выполнилось 4 (четыре) раза? Лучше освой написание JOIN-ов. |
||||
15 май 13, 13:34 [14298915] Ответить | Цитировать Сообщить модератору |
butthead
Guest |
Реализовал пока через табличную переменную, близко к вот этому. Добавил еще один union на случай, когда товар отсутствует в таблице неликвидов.declare @t table(good char(10),deldate date, qty int, cost money,price money, begQ int) insert @t select s1.* , isnull( sum( s2.qty ), 0 ) begQ from sales s1 left join sales s2 on s1.good = s2.good and s1.deldate > s2.deldate group by s1.good, s1.deldate, s1.qty, s1.cost, s1.price select t.good, t.deldate, t.qty, t.cost, t.price, 1 is_surplus from @t t,surplus s where s.qty >= t.begQ+t.qty and t.good = s.good union all select t.good, t.deldate, t.qty, t.cost, t.price, 0 is_surplus from @t t ,surplus s where t.good = s.good and s.qty < t.begQ union all select t.good, t.deldate, s.qty - t.begQ, t.cost, t.price, 1 is_surplus from @t t,surplus s where s.qty > t.begQ and s.qty < t.begQ+t.qty and t.good = s.good union all select t.good, t.deldate, t.begQ+t.qty - s.qty, t.cost, t.price, 0 is_surplus from @t t,surplus s where s.qty >= t.begQ and t.good = s.good and s.qty < t.begQ+t.qty union all select t.good, t.deldate, t.qty, t.cost, t.price, 0 is_surplus from @t t where t.good not in (select good from surplus) order by good, deldate, is_surplus desc |
15 май 13, 23:23 [14302446] Ответить | Цитировать Сообщить модератору |
butthead
Guest |
На реальных данных with у меня в 1,5-2 раза быстрее работает.with t(good,deldate,qty,cost,price,begQ)as( select s1.*, sum(isnull(s2.qty,0)) begQ from sales s1 left join sales s2 on s1.good = s2.good and s1.deldate > s2.deldate group by s1.good, s1.deldate, s1.qty, s1.cost, s1.price) select t.good, t.deldate, t.qty, t.cost, t.price, 1 is_surplus from t,surplus s where s.qty >= t.begQ+t.qty and t.good = s.good union all select t.good, t.deldate, t.qty, t.cost, t.price, 0 is_surplus from t ,surplus s where t.good = s.good and s.qty < t.begQ union all select t.good, t.deldate, s.qty - t.begQ, t.cost, t.price, 1 is_surplus from t,surplus s where s.qty > t.begQ and s.qty < t.begQ+t.qty and t.good = s.good union all select t.good, t.deldate, t.begQ+t.qty - s.qty, t.cost, t.price, 0 is_surplus from t,surplus s where s.qty >= t.begQ and t.good = s.good and s.qty < t.begQ+t.qty union all select t.good, t.deldate, t.qty, t.cost, t.price, 0 is_surplus from t where t.good not in (select good from surplus) order by good, deldate, is_surplus desc |
16 май 13, 11:55 [14304370] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |