在SQL Server

Question

请参阅该表下方的两列得到计算结果。

|RefNbr | DocDate  | OrigAmt | AdjAmt  | Balances | 
|INV001 | 2016-03-15 | 5,000.00 | 250.00 | 4,750.00 | 
|INV002 | 2016-03-16 | 5,000.00 | 750.00 | 4,000.00 | 
|INV003 | 2016-03-17 | 5,000.00 | 1,000.00 | 3,000.00 | 
|INV004 | 2016-03-19 | 5,000.00 | 500.00 | 2,500.00 | 

如何提供查询来获取余额的价值？ （余额= OrigAmt - AdjAmt（此规则仅对于第一行），然后在第二行中，余额=上一个余额（在第一行余额） - AdjAmt，和等等）。

Answer 1

下面是使用窗口聚合函数

select OrigAmt - sum(AdjAmt) over(order by DocDate asc) as Balances 
From yourtable 

对小于的SQL Server 2012使用下面的代码本

SELECT OrigAmt - cum_sum AS Balances 
FROM yourtable a 
     CROSS apply (SELECT Sum(AdjAmt) 
        FROM yourtable b 
        WHERE b.DocDate <= a.DocDate) cs(cum_sum) 

Answer 2

尝试，它可以帮助你少的一种方式。

**

CREATE TABLE #TAB(REFNBR VARCHAR(MAX),DOCDATE DATETIME ,ORIGAMT DECIMAL(18,2),ADJAMT DECIMAL(18,2)) 
INSERT INTO #TAB VALUES ('INV001','2016-03-15',5000.00,250.00),('INV002','2016-03-16',5000.00,750.00), 
('INV003','2016-03-17',5000.00,1000.00),('INV004','2016-03-19',5000.00,500.00)  


      ;WITH CTE AS (
SELECT REFNBR, 
     DOCDATE, 
     ORIGAMT, 
     ADJAMT, 
     ORIGAMT-ADJAMT AS BALANCE, 
     ROW_NUMBER() OVER (ORDER BY DOCDATE) AS RN 
FROM #TAB) 
SELECT a.REFNBR, 
     a.DOCDATE, 
     a.ORIGAMT, 
     a.ADJAMT, 
     CASE WHEN ISNULL(LAG(a.BALANCE + ISNULL(x.ADDS,0)) OVER (ORDER BY a.RN),0) + a.ORIGAMT - a.ADJAMT < 0 
      THEN 0 
      ELSE a.BALANCE + ISNULL(x.ADDS,0) 
     END AS FINAL_BALANCE 
FROM CTE a 
CROSS APPLY (SELECT SUM(BALANCE) AS ADDS 
       FROM CTE f 
        WHERE f.REFNBR = a.REFNBR AND f.RN < a.RN 
        ) x 

**

上面的代码是2014年更少于2014年尝试下面的代码，一旦

   SELECT REFNBR, 
     DocDate, 
     OrigAmt, 
     AdjAmt, 
     CASE 
     WHEN RNO > 1 THEN Sum(OrigAmt - ADJAMT) 
          OVER(
           PARTITION BY REFNBR 
           ORDER BY RNO) 
     ELSE Iif((OrigAmt - ADJAMT) < 0, 0, OrigAmt - ADJAMT) 
     END 
FROM (SELECT *, 
       Row_number() 
       OVER(
        PARTITION BY REFNBR 
        ORDER BY DocDate) AS RNO 
     FROM #TAB) A