代码之家 › 专栏 › 技术社区 › Doonie Darkoo

如何从表中获取加减

sql-server

Doonie Darkoo · 技术社区 · 6 年前

我想计算每次添加新项目时在销售订单中添加的数量以及取消的项目的价值。

CREATE TABLE SaleOrder
    (
    TransactionNo Int,
    SaleOrderDate DATE,
    Code VARCHAR(25),
    Quantity INT,
    TotalAmount Numeric(18,2),
    Remarks VARCHAR(25)
    )

INSERT INTO SaleOrder VALUES (NULL, '2018-10-01', 'SO-001-OCT-18', 6, '2500', 'Hello');
INSERT INTO SaleOrder VALUES (1, '2018-10-01', 'SO-001-OCT-18', 8, '2600', 'Hello');
INSERT INTO SaleOrder VALUES (2, '2018-10-01', 'SO-001-OCT-18', 12, '3400', 'Hello');
INSERT INTO SaleOrder VALUES (3, '2018-10-01', 'SO-001-OCT-18', 9, '2900', 'Hello');

这将是我期待的结果。

Code           SaleOrderDate  Quantity InitialAmount Addition   Cancellation
SO-001-OCT-18  2018-10-01     9        2500.00       900.00     500.00

我写了这个查询,但没什么帮助。

;WITH CTE AS (
SELECT 
[TransactionNo], [Code], [SaleOrderDate], [Quantity], [TotalAmount],
CAST('Oct  1 2018 10:16AM' AS DATE) AS [DateFrom], CAST('Oct  4 2018 10:16AM' AS DATE) AS [DateTo]

FROM [SaleOrder]

GROUP BY 
[TransactionNo], [Code], [SaleOrderDate], [TotalAmount], Quantity
)

SELECT 
    [D].[TransactionNo], [D].[Code], [D].[SaleOrderDate], [D].[Quantity], [D].TotalAmount, 

    --CAST('Oct  4 2018  4:06PM' AS DATE) AS [DateFrom],
    --CAST('Oct  4 2018  4:06PM' AS DATE) AS [DateTo],   
    [D].[Balance], [D].[Balance]-ISNULL(NULLIF([D].TotalAmount, 0),0) [Opening]

FROM(
    SELECT *,
           SUM(TotalAmount) OVER (PARTITION BY [Code] ORDER BY [TransactionNo], [SaleOrderDate]) AS [Balance]
    FROM CTE 

    )D

    WHERE [SaleOrderDate] BETWEEN CAST('Oct  1 2018 10:16AM' AS DATE) AND CAST('Oct  4 2018 10:16AM' AS DATE)

    ORDER BY [SaleOrderDate]

2 回复 | 直到 6 年前

Squirrel 6 年前

LAG()

; WITH cte as
(
    SELECT  *,
            row_no      = ROW_NUMBER() OVER (PARTITION BY Code ORDER BY TransactionNo DESC),
            Addition    = CASE WHEN TotalAmount > LAG(TotalAmount) OVER (PARTITION BY Code ORDER BY TransactionNo)
                               THEN TotalAmount - LAG(TotalAmount) OVER (PARTITION BY Code ORDER BY TransactionNo)
                               ELSE 0
                               END,
            Cancellation = CASE WHEN    TotalAmount < LAG(TotalAmount) OVER (PARTITION BY Code ORDER BY TransactionNo)
                                THEN    LAG(TotalAmount) OVER (PARTITION BY Code ORDER BY TransactionNo) - TotalAmount
                                ELSE    0
                                END
    FROM    SaleOrder
)
SELECT  Code,  
        SaleOrderDate,
        Quantity    = MAX (CASE WHEN row_no  = 1 then Quantity END),
        InitialAmount   = MAX (CASE WHEN TransactionNo IS NULL THEN TotalAmount END),
        Addition    = SUM (Addition),
        Cancellation    = SUM (Cancellation)
FROM    cte
GROUP BY Code, SaleOrderDate

iSR5 6 年前

你想这么做吗

SELECT 
    Code
,   MAX(SaleOrderDate) SaleOrderDate
,   MAX(Quantity) Quantity
,   MAX(InitialAmount) InitialAmount
,   SUM(Addition) Addition
,   ABS(SUM(Cancellation)) Cancellation
FROM (
SELECT 
    Code
,   CASE WHEN rn = cnt THEN SaleOrderDate END SaleOrderDate
,   CASE WHEN rn = cnt THEN Quantity END Quantity
,   InitialAmount
,   CASE WHEN Diff > 0 THEN Diff ELSE 0 END Addition
,   CASE WHEN Diff < 0 THEN Diff ELSE 0 END Cancellation
FROM (
SELECT *
,   CASE WHEN TransactionNo IS NULL THEN TotalAmount END InitialAmount
,   LEAD(TotalAmount) OVER(PARTITION BY Code ORDER BY TransactionNo)  nxtPrice
,   LEAD(TotalAmount) OVER(PARTITION BY Code ORDER BY TransactionNo) - TotalAmount Diff
,   COUNT(*) OVER(PARTITION BY Code) cnt 
,   ROW_NUMBER() OVER(PARTITION BY Code ORDER BY SaleOrderDate) rn 
FROM SaleOrder 
) D 
) C 
GROUP BY 
    Code