sql - SQL Server 2012如何计算各个阶段每个id的总支付金额并减少每个阶段后的支付金额

Question

我有两个表 TableA 和 TableB。

• TableA 将总需求(列名 -> 金额)拆分为多行。
• TableB 有供应编号(列名 -> 数量)。

我想拆分供应编号并将它们分配给 TableA 中的每一行，为每个 id(列名 -> 编号)。

TableA和TableB的示例数据如下:

表A:

row number  amount
-------------------
1     x       10
2     y        5
3     z       120
4     z        80
5     z         5

表B:

number  amount
---------------
  x       5 
  y       15
  z       200

要求的输出是:

row number  amount  paid
-------------------------
1     x        10     5
2     y         5     5
3     z       120   120
4     z        80    80
5     z         5     0

截至目前，我们正在使用下面提到的代码，该代码非常丑陋并且性能不佳，因为我们使用递归 cte 来完成这项工作，因为我们的系统是 SQL Server 2008 R2我们别无选择，但现在我们的软件已升级到 SQL Server 2012，我知道可以使用带有 order by in over 子句的求和函数来实现同样的效果。但是我不知道怎么做？

with cte as 
(
    select 
        a.row, a.number, a.amount,
        b.amount as total
    from 
        tableA as a
    left join 
        tableB as b on a.number = b.number
),
cte1 as 
(
    select 
        row, number, amount, total,
        case 
           when amount - total < 0 then amount else total 
        end as paid,
        case 
           when amount - total < 0 then amount else total 
        end as totalpaid
    from 
        cte
    where 
        row = 1

    union all

    select 
        b.row, b.number, b.amount, b.total,
        case
           when b.amount - (b.total - (case when b.number = a.number then a.totalpaid else 0 end)) < 0 then b.amount else (b.total - (case when b.number = a.number then a.totalpaid else 0 end)) end,
           case when b.amount - (b.total - (case when b.number = a.number then a.totalpaid else 0 end)) < 0 then b.amount else (b.total - (case when b.number = a.number then a.totalpaid else 0 end)) end + ((case when b.number = a.number then a.totalpaid else 0 end))
    from 
        cte1 as a
    inner join 
        cte as b on b.row = a.row + 1
)
select 
    row, number, amount, paid
from 
    cte1

有人能告诉我如何在 SQL Server 2012 中高效地编写上述代码吗？

提前致谢。

Answer 1

试试这段代码:

WITH cte as
(
    SELECT a.row,
        a.number, 
        a.amount,
        b.amount AS totalPaid,
        SUM(a.amount) OVER (PARTITION BY a.number ORDER BY row ROWS UNBOUNDED PRECEDING) AS totalAmount
            FROM (VALUES (1,'x',10),(2,'y',5),(3,'z',120),(4,'z',80),(5,'z',5)) AS a(row, number, amount)
            LEFT JOIN (VALUES ('x',5),('y',15),('z',200)) as b(number, amount) ON a.number = b.number
)
SELECT row, number, amount, CASE WHEN totalPaid >= totalAmount THEN amount ELSE CASE WHEN amount - totalAmount + totalPaid < 0 THEN 0 ELSE amount - totalAmount + totalPaid END END AS paid
    FROM cte;

请给我有关正确性和性能改进的反馈。