sql - 在 SQL 查询中实现借贷解析系统

Question

我正在尝试实现借记贷记解决系统，但我很难表达基于集合的逻辑。

假设我有一张订单表:

Id    OrderId     Amount     AdjustmentFlag
 1       1           10.00        0
 2       1           10.00        1
 3       1           10.00        2
 4       2           20.00        1
 5       2           20.00        2
 6       2           20.00        2
 7       3           30.00        1
 8       4           40.00        0
 9       4           40.00        0
10       4           40.00        1
11       5           50.00        0
12       5           50.00        1
13       5           60.00        2
14       5           60.00        1
15       5           60.00        2
16       5           70.00        1

我需要根据它们是否具有匹配的“已取消”标志来挑选出仍然有效的 Id。

0 - Original Order
1 - Cancelled Order
2 - Adjusted Order

1. 1 匹配 0 或 2，优先于 0。
2. 如果没有匹配项，则忽略 1 标志。

给定上面的例子:

• Id 2 将匹配 Id 1，剩下 Id 3。
• Id 4 将匹配 Id 5 或 Id 6，但不能同时匹配两者。
• Id 7 将被忽略。
• Id 10 将匹配 Id 8 或 Id 9，但不能同时匹配两者。
• Id 12 将匹配 Id 11。
• Id 14 将匹配 Id 13 或 Id 15，但不能同时匹配两者。
• Id 16 将被忽略。

可能的结果是 [1, 2, 4, 5, 7, 8, 10, 11, 12, 13, 14, 16](较低的 Id 优先)或 [1, 2, 4, 6, 7 , 9, 10, 11, 12, 14, 15, 16](较高的 Id 优先)。只要结果是确定的，两者都可以。

创建脚本:

CREATE TABLE [Order]
(
     Id INT IDENTITY NOT NULL PRIMARY KEY
    ,OrderId INT NOT NULL
    ,Amount MONEY NOT NULL
    ,AdjustmentFlag TINYINT NOT NULL
);

INSERT INTO [Order](OrderId, Amount, AdjustmentFlag)
SELECT 1, 10.00, 0
UNION ALL
SELECT 1, 10.00, 1
UNION ALL
SELECT 1, 10.00, 2
UNION ALL
SELECT 2, 20.00, 1
UNION ALL
SELECT 2, 20.00, 2
UNION ALL
SELECT 2, 20.00, 2
UNION ALL
SELECT 3, 30.00, 1
UNION ALL
SELECT 4, 40.00, 0
UNION ALL
SELECT 4, 40.00, 0
UNION ALL
SELECT 4, 40.00, 1
UNION ALL
SELECT 5, 50.00, 0
UNION ALL
SELECT 5, 50.00, 1
UNION ALL
SELECT 5, 60.00, 2
UNION ALL
SELECT 5, 60.00, 1
UNION ALL
SELECT 5, 60.00, 2
UNION ALL
SELECT 5, 70.00, 1

这是我目前的部分解决方案:

WITH Orders AS
(
    SELECT
        Id,
        OrderId,
        Amount,
        AdjustmentFlag,
        EffectiveOrder = ROW_NUMBER() OVER (PARTITION BY OrderId, Amount ORDER BY AdjustmentFlag DESC),
        UnmatchedOrder = CASE WHEN EXISTS(SELECT 1 FROM [Order] uo WHERE uo.OrderId = o.OrderId GROUP BY uo.OrderId HAVING(COUNT(uo.OrderId) = 1)) THEN 1 ELSE 0 END,
        OriginalWithoutAdjustment = CASE WHEN EXISTS(SELECT 1 FROM [Order] uo WHERE uo.OrderId = o.OrderId AND uo.Amount = o.Amount GROUP BY uo.OrderId, uo.Amount HAVING (MAX(uo.AdjustmentFlag) = 1)) THEN 1 ELSE 0 END,
        AdjustmentWithoutOriginal = CASE WHEN EXISTS(SELECT 1 FROM [Order] uo WHERE uo.OrderId = o.OrderId AND uo.Amount = o.Amount GROUP BY uo.OrderId, uo.Amount HAVING (MIN(uo.AdjustmentFlag) = 1)) THEN 1 ELSE 0 END
    FROM [Order] o
)
,MatchedOrders AS
(
    SELECT
        Id
    FROM Orders
    WHERE
    -- Assume AdjustmentFlag = 2 and take everything else
    EffectiveOrder <> 1
    OR
    (
        -- Assume AdjustmentFlag = 2 and there is no Order with AdjustmentFlag = 0
        -- Take everything since the MIN AdjustmentFlag = 1
        AdjustmentWithoutOriginal = 1
        AND EffectiveOrder > 1
    )
    OR
    (
        -- Assume AdjustmentFlag = 1 and there are no other Orders, so ignore it
        AdjustmentFlag = 1
        AND UnmatchedOrder = 1
    )
    OR
    (
        -- We don't care about the orders if they don't have any Amount
        Amount = 0
        AND EffectiveOrder = 1
    )
    AND NOT
    (
        -- We have an Original without any other Orders
        EffectiveOrder = 1
        AND UnmatchedOrder = 1
        AND AdjustmentFlag = 0
    )
)
SELECT
    o.OrderId,
    o.AdjustmentFlag,
    o.Amount,
    o.EffectiveOrder,
    o.UnmatchedOrder,
    Excluded = CASE WHEN mo.Id IS NULL THEN 0 ELSE 1 END
FROM Orders o
LEFT OUTER JOIN MatchedOrders mo
ON o.Id = mo.Id
ORDER BY OrderId, Amount, AdjustmentFlag

结果:

Answer 1

尝试:

with cte as
(select o.*, 
        case AdjustmentFlag when 1 then -1 else 1 end DrCr,
        row_number() over (partition by OrderId, Amount, case AdjustmentFlag when 1 then 1 end
                           order by AdjustmentFlag, Id) Rn
 from [Order] o)
select OrderId,
       max(case DrCr when 1 then Id end) DrId,
       sum(case DrCr when 1 then Amount else 0 end) DrAmount,
       max(case DrCr when 1 then AdjustmentFlag end) DrAdjustmentFlag,
       max(case DrCr when -1 then Id end) CrId,
       sum(case DrCr when -1 then Amount else 0 end) CrAmount,
       max(case DrCr when -1 then AdjustmentFlag end) CrAdjustmentFlag,
       sum(DrCr * Amount) BalanceAmount
from cte
group by OrderId, Amount, Rn
having sum(DrCr * Amount) >= 0 /* excludes unmatched cancelled orders */

- 如果您只想查看未匹配的原始/修改订单，请将 having 子句条件更改为 > 0。

SQLFiddle here .