Блог

пожалуйста, предложите мне способ ведения таблицы запасов, которая будет автоматически обновляться при вставке данных в таблицы покупок или продаж, может произойти добавление или обновление нескольких строк, на данный момент я использую приведенный ниже запрос для создания записей запасов: что-то вроде: запасы.кол-во = сумма(покупка.кол-во) — сумма(продажа.кол-во);

 select ProductName, 
       sum(PQty),
       sum(SQty),
       sum(PQty - SQty)
from (
  select p.product_name as ProductName,
         0 as SQty,
         case when sum(p.qty) is null then 0 else sum(p.qty) end as PQty from purchase p
  group by p.product_name
  
  union
  
  select s.product_name as ProductName, 
         0 as PQty, 
         case when sum(s.qty) is null then 0 else sum(s.qty) end as SQty from sell s
  group by s.product_name
) Stocks
group by ProductName;
 

Чтобы получить ценность товаров, не имеющих продаж, вам нужно СЛЕВА ПРИСОЕДИНИТЬ покупки к продажам, а не пытаться объединить их. Вы можете сделать это, создав два CTE, которые получают общее количество покупок и общее количество проданных соответственно. Затем вы оставили присоединиться к CTE на product_name. Что-то вроде:

     with pur(product_name,total_purchased)  as 
         ( select product_name
                , sum(qty) 
             from purchase 
            group by product_name
         ) 
       , sal (product_name,total_sold) as   
        ( select  product_name 
                , sum(qty) 
             from sell 
            group by product_name
         )                  
    select p.Product_Name
         , p.total_purchased
         , coalesce(s.total_sold,0.00) total_sold
         , p.total_purchased -  coalesce(s.total_sold,0.00) stock
      from pur p 
      left join sal s 
        on (p.product_name = s.product_name);     
 

Это, однако, НЕ поддерживает уровни запасов, но создает подсчет на данный момент.

Я предлагаю отказаться от идеи «поддерживать таблицу запасов, которая будет автоматически обновляться …». Поскольку ведение таблицы усложняется, вам нужно обрабатывать не только вставки, но также обновления и удаления, что усложняется. Что происходит, когда покупка отменяется или в продаже есть дефектный продукт,… В основном вы храните выводимое значение, что обычно является очень плохой идеей.
Вместо этого создайте ПРЕДСТАВЛЕНИЕ, которое при необходимости предоставляет значения «подсчета в данный момент». И по самой своей природе АВТОМАТИЧЕСКИ подстраивается под любую активность на любом столе.

 create view stock( product_name   
                      , purchases     
                      , sales         
                      , current_stock  
                      ) as 
    with pur(product_name,total_purchased)  as 
         ( select product_name
                , sum(qty) 
             from purchase 
            group by product_name
         ) 
       , sal (product_name,total_sold) as   
        ( select  product_name 
                , sum(qty) 
             from sell
            group by product_name
         )                  
    select p.Product_Name
         , p.total_purchased
         , coalesce(s.total_sold,0.00) 
         , p.total_purchased -  coalesce(s.total_sold,0.00) 
      from pur p 
      left join sal s 
        on (p.product_name = s.product_name);   
 

См. демонстрацию: Для демонстрации я попытался сгенерировать данные, чтобы для любого продукта количество проданных товаров не превышало количество купленных. Однако значения генерируются с помощью функции random (), поэтому это не гарантируется.