时间戳

首页 > 解决方案 > 如何进行查询，按日期从行中减去值并从下一行中减去剩余值

c# - 如何进行查询，按日期从行中减去值并从下一行中减去剩余值

问题描述

我在 sql 中有一个表，其中项目存储有到期日期和金额，我想进行查询以减去到期日期最小的金额列中的一些金额。

“例如：

update table
    set amountcolumn = amountcolumn - amount
    where Expiry Date = MIN(ExpiryDate)

如果输入的金额大于列中的金额，则应从行中减去所有值，从表中删除该行，剩余值应从到期日期为下一个最小值的下一行中减去。

当用户输入的金额大于它给出的金额列时，我已经尝试过这个查询，结果是金额列中的减号。

CREATE PROCEDURE [dbo].[UpdateInventory]
(
    @ItemName       NVARCHAR(50),
    @InventoryAmount DECIMAL(18,0),
    @TotalAmount     DECIMAL(18,0),
    @UpdatedBy      INT,
    @UpdatedName    NVARCHAR(50),
    @UpdatedDate    DATE,
    @UpdatedTime    TIME(2)
)
AS
BEGIN
    DECLARE @ExpiryDate DATE;
    SET @ExpiryDate = (SELECT MIN(ExpiryDate) FROM Inventry)
    UPDATE Inventry 
    SET InventoryAmount = (InventoryAmount - @InventoryAmount), TotalCost = (TotalCost - @TotalAmount),
        UpdatedBy = @UpdatedBy, UpdatedName = @UpdatedName, UpdatedDate = @UpdatedDate, UpdatedTime = @UpdatedTime
    WHERE ExpiryDate = @ExpiryDate AND ItemName = @ItemName
END

我希望输出如果 row1 有 10 row2 有 20 并且用户输入 23 它应该从 row1 中减去 10 并从 row2 中减去 13 并且应该删除 row1

标签： c#sql

解决方案

sum() over()带有(sql-server) 的FIFO 。

create table foo (
    product varchar(100),
    amount int,
    expires date
);

insert foo values
     ('Potato',50,'2019-10-03') 
    ,('Tomato',10,'2019-10-03')
    ,('Tomato',20,'2019-11-04');

查询

--  update
declare @tomatoAmount int = 25;

with t as (
  select f.*,
  sum(amount) over (partition by product order by expires ) as runningtotal
  from foo f
)
update t set amount = case when runningtotal - @tomatoAmount <= 0 then 0 
                           else (runningtotal - @tomatoAmount)
                      end
where product = 'tomato' and runningtotal - @tomatoAmount <= amount;

select * from foo;

-- delete

delete 
from foo 
where amount = 0;

select * from foo;

推荐阅读