sql - 在 join in sql 中计算

Question

我正在尝试在 sql 2008 的连接中实现交错计算。我可以为 1 个作业 ID 设置 n 行。我在下面创建了一个示例

CREATE TABLE Job
(
  JobID INT NOT NULL,
  Amount INT NOT NULL
);

INSERT INTO Job (JobID, Amount)
    VALUES (1, 25),
        (1, 45),
        (1, 40),
        (2, 25),
        (3, 26),
        (3, 26);

现在折扣 JobID = 1是 80 ，所以我期望查询结果的输出如下:

如果Amount > Discount , 所以显示 finalvalue = Amount - Discount但如果Amount < Discount , 然后显示 Finalvalue = Amount - Amount ,如果Discount还剩下 ，从后面的行中扣除相同的数。

Job ID  Amount  FinalValue
1        25       0
1        45       0
1        40      30

所有这些都可以在一个连接中完成吗？

Answer 1

给你:

编辑:注意:您应该添加一个用于排序的列。我的方法是按 JobID 进行分区和排序，这使得输出随机...

编辑:抱歉，没有添加表格...

CREATE TABLE Job
(
    JobID INT NOT NULL,
    Amount INT NOT NULL
);

INSERT INTO Job (JobID, Amount)
    VALUES (1, 25), (1, 45), (1, 40), (2, 25), (3, 26), (3, 26);

CREATE TABLE Discount
(
    JobID INT NOT NULL,
    Discount INT NOT NULL
);
INSERT INTO Discount(JobID,Discount)VALUES(1,80),(2,0),(3,10);
    WITH myCTE AS
    (
        SELECT ROW_NUMBER() OVER(PARTITION BY Job.JobID ORDER BY Job.JobID) AS inx
              ,Job.JobID
              ,Job.Amount
              ,Discount.Discount     
        FROM Job
        INNER JOIN Discount ON Job.JobID=Discount.JobID
    )
    SELECT * FROM myCTE
    CROSS APPLY
    (
        SELECT SUM(x.Amount)
        FROM myCTE AS x
        WHERE x.JobID=myCTE.JobID
          AND x.inx<=myCTE.inx  
    ) AS AmountCummulativ(AmountCummulativ)
    CROSS APPLY(SELECT AmountCummulativ-myCTE.Discount) AS DiscountCalculated(DiscountCalculated)
    CROSS APPLY(SELECT CASE WHEN DiscountCalculated<0 THEN 0 ELSE DiscountCalculated END) AS DiscountResolved(DiscountResolved)

希望对你有帮助