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

Откуда:
Сообщений: 16
 		Добрый вечер.
Есть 3 таблицы номенклатура(nomenclature),заказы(orders),склад (storage)
DECLARE @nomenclature TABLE ( nomenclature_id INT, nomenclature_marka VARCHAR(100),nomenclature_model VARCHAR(100),nomenclature_type VARCHAR(100))
INSERT INTO @nomenclature 
VALUES
  ( 1, 'Audi','A6','салон' ),
  ( 2, 'Audi','Q5','салон' ),
  ( 3, 'Audi','Q5','багажник' ),
  ( 4, 'Audi','Q7','салон' )
  ;
DECLARE @orders TABLE ( orders_id INT, nomenclature_id INT,orders_qty INT )
INSERT INTO @orders 
VALUES
  ( 1, 1, 10 ),
  ( 2, 3, 4 )
;
DECLARE @storage TABLE ( storage_id INT, nomenclature_id INT,storage_qty INT )
INSERT INTO @storage 
VALUES
  ( 1, 1, 10 ),
  ( 2, 2, 9 ),
  ( 3, 3, 8 ),
  ( 4, 4, 7 )
;


Как получить следующее (т.е. склад - заказы с группировкой по nomenclature_marka и nomenclature_type ):
nomenclature_markaвсего салонвсего багажник всего
Audi16420


Спасибо.
24 июн 18, 20:15    [21517084]     Ответить | Цитировать Сообщить модератору
 Re: Группировка  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30825
gigar
Как получить следующее (т.е. склад - заказы с группировкой по nomenclature_marka и nomenclature_type ):

DECLARE @nomenclature TABLE ( nomenclature_id INT, nomenclature_marka VARCHAR(100),nomenclature_model VARCHAR(100),nomenclature_type VARCHAR(100))
INSERT INTO @nomenclature 
VALUES
  ( 1, 'Audi','A6','салон' ),
  ( 2, 'Audi','Q5','салон' ),
  ( 3, 'Audi','Q5','багажник' ),
  ( 4, 'Audi','Q7','салон' )
  ;
DECLARE @orders TABLE ( orders_id INT, nomenclature_id INT,orders_qty INT )
INSERT INTO @orders 
VALUES
  ( 1, 1, 10 ),
  ( 2, 3, 4 )
;
DECLARE @storage TABLE ( storage_id INT, nomenclature_id INT,storage_qty INT )
INSERT INTO @storage 
VALUES
  ( 1, 1, 10 ),
  ( 2, 2, 9 ),
  ( 3, 3, 8 ),
  ( 4, 4, 7 )
;

select	n.nomenclature_marka, 
		sum(case when n.nomenclature_type = 'салон' then s.storage_qty else 0 end) - sum(case when n.nomenclature_type = 'салон' then o.orders_qty else 0 end) as [всего салон],
		sum(case when n.nomenclature_type = 'багажник' then s.storage_qty else 0 end) - sum(case when n.nomenclature_type = 'багажник' then o.orders_qty else 0 end) as [всего багажник],
		sum(s.storage_qty) - sum(o.orders_qty) as [Всего]
from @nomenclature n
	left join @orders o
		on o.nomenclature_id = n.nomenclature_id
	left join @storage s
		on s.nomenclature_id = n.nomenclature_id
group by n.nomenclature_marka
24 июн 18, 21:01    [21517157]     Ответить | Цитировать Сообщить модератору
 Re: Группировка  [new]
gigar
Member

Откуда:
Сообщений: 16
 		Проблема в том, что в таблице склад(storage) имеются записи(nomenclature_id), которых нет в заказах (orders).
А в заказах nomenclature_id может быть несколько раз одинаковые.
24 июн 18, 23:21    [21517375]     Ответить | Цитировать Сообщить модератору
 Re: Группировка  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30825
gigar
Проблема в том, что в таблице склад(storage) имеются записи(nomenclature_id), которых нет в заказах (orders).
А в заказах nomenclature_id может быть несколько раз одинаковые.
Эээ, разве у меня это не учитывается?

Вы поправьте тогда ваши тестовые данные для описанных вами случаев, посмотрим, поправим, если что не так.
24 июн 18, 23:33    [21517394]     Ответить | Цитировать Сообщить модератору
 Re: Группировка  [new]
gigar
Member

Откуда:
Сообщений: 16
 		Если продублировать последнюю строку в заказах, то вместо вычитания (склад-заказы) происходит ,почему-то,суммирование
25 июн 18, 00:03    [21517446]     Ответить | Цитировать Сообщить модератору
 Re: Группировка  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
 
select	n.nomenclature_marka, 
		sum(case when n.nomenclature_type = 'салон' then isnull(s.storage_qty,0) - isnull(o.orders_qty,0) else 0 end) as [всего салон],
		sum(case when n.nomenclature_type = 'багажник' then isnull(s.storage_qty,0) - isnull(o.orders_qty,0) else 0 end) as [всего багажник],
		sum(isnull(s.storage_qty,0) - isnull(o.orders_qty,0)) as [Всего]
from @nomenclature n
	outer apply(Select Sum(orders_qty) as orders_qty from @orders o Where o.nomenclature_id = n.nomenclature_id) o
	outer apply(Select Sum(storage_qty) as storage_qty from @storage s Where s.nomenclature_id = n.nomenclature_id) s
Group by n.nomenclature_marka



select	n.nomenclature_marka, 
		sum(case when n.nomenclature_type = 'салон' then isnull(os.storage_qty,0) - isnull(os.orders_qty,0) else 0 end) as [всего салон],
		sum(case when n.nomenclature_type = 'багажник' then isnull(os.storage_qty,0) - isnull(os.orders_qty,0) else 0 end) as [всего багажник],
		sum(isnull(os.storage_qty,0) - isnull(os.orders_qty,0)) as [Всего]
from @nomenclature n
left join
	(Select nomenclature_id, null as orders_qty, storage_qty From @storage
      union all
	 Select nomenclature_id, orders_qty, null as storage_qty From @orders) os on os.nomenclature_id = n.nomenclature_id
Group by n.nomenclature_marka
25 июн 18, 08:06    [21517632]     Ответить | Цитировать Сообщить модератору
 Re: Группировка  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30825
gigar
Если продублировать последнюю строку в заказах, то вместо вычитания (склад-заказы) происходит ,почему-то,суммирование
А, да, ошибка.

Нужно сгруппировать по nomenclature_id

Как это сделал Kopelly, или так:

select	n.nomenclature_marka, 
		sum(case when n.nomenclature_type = 'салон' then s.storage_qty else 0 end) - sum(case when n.nomenclature_type = 'салон' then o.orders_qty else 0 end) as [всего салон],
		sum(case when n.nomenclature_type = 'багажник' then s.storage_qty else 0 end) - sum(case when n.nomenclature_type = 'багажник' then o.orders_qty else 0 end) as [всего багажник],
		sum(s.storage_qty) - sum(o.orders_qty) as [Всего]
from @nomenclature n
	left join (select nomenclature_id, sum(orders_qty) as orders_qty from @orders o group by nomenclature_id) as o
		on o.nomenclature_id = n.nomenclature_id
	left join (select nomenclature_id, sum(storage_qty) as storage_qty from @storage s group by nomenclature_id) as s
		on s.nomenclature_id = n.nomenclature_id
group by n.nomenclature_marka
25 июн 18, 08:51    [21517661]     Ответить | Цитировать Сообщить модератору
 Re: Группировка  [new]
gigar
Member

Откуда:
Сообщений: 16
 		Спасибо за помощь.
25 июн 18, 09:41    [21517747]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить