Блог

Может кто-нибудь посоветовать мне, как создать сводку с некоторыми вычислениями? Простой СВОД с «реальными данными» не проблема, но я понятия не имею, как включить некоторые вычисления, даже такие простые, как, например, вычитание значений между некоторыми столбцами…

Пожалуйста, если вы сможете, попробуйте использовать пример таблиц и данных ниже…

 -- Structure
-- ---------

-- Table of Products
CREATE TABLE ListProducts
(
    ProductID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(25)
);

-- Table of Processes
CREATE TABLE ListProcesses
(
    ProcessID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(25)
);

-- DataTable
CREATE TABLE Production
(
    ProductionID INT IDENTITY(1,1) PRIMARY KEY,
    ProductID INT FOREIGN KEY REFERENCES ListProducts(ProductID),
    ProcessID INT FOREIGN KEY REFERENCES ListProcesses(ProcessID),
    Amount INT
);

-- Data
-- ----

INSERT INTO ListProducts(Name) VALUES ('Product1'),('Product2'),('Product3');

INSERT INTO ListProcesses(Name) VALUES ('Process1'),('Process2'),('Process3');

INSERT INTO Production(ProductID,ProcessID,Amount) VALUES
(1,1,25),(2,1,15),(3,1,20),(1,2,10),(2,2,10),(3,2,5),(1,3,5),(2,3,5);
  

Я хотел бы получить СВОДКУ, подобную:

 | Products | Process1 | Process2 | Process3 |
| Product1 |       15 |        5 |        5 |
| Product2 |        5 |        5 |        5 |
| Product3 |       15 |        5 |     NULL |
  

Как вы можете видеть — вывод не похож на простой свод, но у меня каждый ‘предыдущий’ столбец вычитается из ‘следующего’ столбца (за исключением последнего столбца, конечно)…

Большое вам спасибо! 🙂

Вы можете использовать условную агрегацию:

 select lpr.name,
       sum(case when p.processid = 1 then p.amount when p.processid = 2 then -p.amount end) as process1,
       sum(case when p.processid = 2 then p.amount when p.processid = 3 then -p.amount end) as process2,
       sum(case when p.processid = 3 then p.amount end) as process3
from production p join
     listproducts lpr
     on p.productid = lpr.productid join
     listprocesses lpro
     on p.processid = lpro.processid
group by lpr.name;
  

Здесь есть db<>fiddle .

Вы можете написать свой запрос следующим образом, используя PIVOT и LEAD .

 select [name] as Products, Process1, Process2, Process3 
from   (select t1.Name, 
               t2.amount - Isnull((Lead(t2.amount) 
                                      over(partition by T2.productid 
                                        order by t2.processid ) ), 0) amount, 
               t3.Name as ProcesName 
        from   listproducts t1 
               inner join production t2 
                       ON t1.productid = t2.productid 
               inner join listprocesses t3 
                       ON t3.processid = t2.processid) t 
       pivot ( max(amount) 
             for procesname in ([Process1], 
                                [Process2], 
                                [Process3]) ) pvt 
  

Онлайн-демонстрация

вы можете попробовать ниже, используя функцию lead()

ДЕМОНСТРАЦИЯ

 with cte as
(
select *,case when processname<>'process3' then 
amount-coalesce(lead(amount) over(partition by productname order by processname),0) else amount end as amt from 
(
select b.name as productname, c.name as processname,amount
from Production a
inner join ListProducts b on a.productid=b.ProductID
inner join ListProcesses c on a.ProcessID=c.ProcessID
)A
)

select productname, max(case when processname='Process1' then amt end) as process1,
max(case when processname='Process2' then amt end) process2,
max(case when processname='Process3' then amt end) process3
from cte 
group by productname
  

ВЫВОД:

 productname process1    process2    process3
Product1    15            5           5
Product2    5             5           5
Product3    15            5