Сформировать SQL запрос (Агрегатные ф-ии)

208

13 декабря 2017, 21:02

Есть 3 таблицы: detail, stock, accounting

Посредством SqlTableAdapter вывожу различные запросы пользователей (через инпуты).

Сейчас потребовалось задать такой запрос, чтобы вывелись все детали такие, что: имеются сведения о наличии этой детали на некотором складе в количестве > 100 и каждая отгрузка этой детали с этого склада была в количестве > 200.

[100 и 200 это понятно спонтанные значения, которые в дальнейшем заменятся @var1 @var2]

По-разному пытался вывести результаты, постоянно получаю что-то не то. Помогите пожалуйста написать запрос

select a.detail_id, sum(a.quantity), b.quantity from STOCK as a, ACCOUNTING as b
where (a.QUANTITY > 100) 
group by a.DETAIL_ID 
having b.QUANTITY > 200

ДОПОЛНЕНИЕ:

пусть

detail.id[0] = 1, stock.stock_num[0]=1, stock.quantity[0] = 125, accounting.quantity[0] = 1230;

detail.id[ 1 ] = 2, stock.stock_num[ 1 ]=5, stock.quantity[ 1 ] = 50, accounting.quantity[ 1 ] = 60;

detail.id[2] = 3, stock.stock_num[2]=3, stock.quantity[2] = 135, accounting.quantity[2] = 1200;

detail.id[3] = 1, stock.stock_num[3]=2, stock.quantity[3] = 145, accounting.quantity[3] = 1300

Тогда в выводе должны отобразиться суммы [0] и [2] и [3] элементов (так-как в 1 элементе не попадает в условие)

И в выводе получаем что-то вроде этого:

id sock_Quantity Accounting_Quantity

1______170______________2530_______

3______135______________1200_______

Answer 1

У вас связь один(STOCK)-ко-многим(ACCOUNTING), значит группировать по a.DETAIL_ID и суммировать a.quantity, когда присоединили еще таблицу ACCOUNTING будет неправильным. Правильнее будет по отдельности сгруппировать таблицу ACCOUNTING по DETAIL_ID и STOCK_NUM, а затем уже соединять его с таблицей STOCK. Примерно так:

SELECT a.detail_id
  , sum(a.quantity)
  , b.SUM_QUANTITY
FROM STOCK as a
JOIN (SELECT STOCK_NUM, DETAIL_ID, SUM(QUANTITY) AS SUM_QUANTITY FROM ACCOUNTING GROUP BY STOCK_NUM, DETAIL_ID) AS b ON a.STOCK_NUM = b.STOCK_NUM AND a.DETAIL_ID = b.DETAIL_ID
WHERE b.SUM_QUANTITY > 200
GROUP BY a.DETAIL_ID 
  , b.SUM_QUANTITY
HAVING SUM(a.QUANTITY) > 100

Если деталь (DETAIL_ID) может присутствовать на разных складах (судя по первоначальному запросу автора), то скорее так:

SELECT a.detail_id
  , sum(a.quantity)
  , SUM(b.SUM_QUANTITY)
FROM STOCK as a
JOIN (SELECT STOCK_NUM, DETAIL_ID, SUM(QUANTITY) AS SUM_QUANTITY FROM ACCOUNTING GROUP BY STOCK_NUM, DETAIL_ID) AS b ON a.STOCK_NUM = b.STOCK_NUM AND a.DETAIL_ID = b.DETAIL_ID
GROUP BY a.DETAIL_ID 
  , b.SUM_QUANTITY
HAVING SUM(a.QUANTITY) > 100 AND SUM(b.SUM_QUANTITY) > 200

ДОПОНЕНИЕ

Судя по дополнению автора, не совсем понятно применение группировки и фильтр по сгруппированным данным. Насколько понятно по дополнениям, фильтрация записей необходима до группировки. Тогда логично использовать либо EXISTS, либо в подзапросах фильтровать STOCK и ACCOUNTING и затем уже соединив их сгруппировать. Либо например так:

SELECT a.detail_id
  , sum(a.quantity)
  , b.SUM_QUANTITY
FROM STOCK as a
CROSS APPLY (SELECT TOP 1 STOCK_NUM, DETAIL_ID, QUANTITY  FROM ACCOUNTING WHERE STOCK_NUM = a.STOCK_NUM AND DETAIL_ID = a.DETAIL_ID AND QUANTITY > 200) AS b
WHERE a.QUANTITY > 100
GROUP BY a.DETAIL_ID