如何基于数量与分组计算订单总金额（SQL存储过程实现）

一、数据表定义 #

CREATE TABLE #MemberTypes
(
    [Id]          INT           NOT NULL IDENTITY(1,1),
    [MemberType]  NVARCHAR (10) NOT NULL,
)

CREATE TABLE #ChargeTypes
(
    [Id]            INT           IDENTITY (1, 1) NOT NULL,
    [ChargeType]    NVARCHAR (45) NOT NULL,
    [isAGroupRate]  bit           NOT NULL
)

CREATE TABLE #MemberTypeChargeType
(
    MemberTypeId   int NOT NULL,
    ChargeTypeId   int NOT NULL, 
    CONSTRAINT [PK_#MemberTypeChargeType] 
        PRIMARY KEY ([MemberTypeId], [ChargeTypeId])
)

CREATE TABLE #ChargeRates
(
    [Id]           INT            IDENTITY (1, 1) NOT NULL,
    [ChargeTypeId] INT            NOT NULL,
    [EarlyBird]    BIT            DEFAULT ((0)) NOT NULL,
    [FromCount]    SMALLINT       NOT NULL,
    [ToCount]      SMALLINT       NOT NULL,
    [RatePrice]    DECIMAL (6, 2) NOT NULL,
    CONSTRAINT [PK_#ChargeRate_Id] 
        PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [UX_#ChargeType_EarlyBird_FromCount] 
        UNIQUE NONCLUSTERED ([ChargeTypeId] ASC, [EarlyBird] ASC, [FromCount] ASC)
);


INSERT INTO #MemberTypes(MemberType)
VALUES ('Regular'), ('Doctor')
    
INSERT INTO #ChargeTypes(ChargeType, [isAGroupRate])
VALUES ('Regular', 0), ('Service People', 0), ('Reciprocation', 1)

INSERT INTO #MemberTypeChargeType(MemberTypeId, ChargeTypeId)
VALUES (2, 2), (1, 1), (1, 3)
    
INSERT INTO #ChargeRates([ChargeTypeId],[EarlyBird],[FromCount],[ToCount],[RatePrice])
VALUES (3, 1, 10, 25, 65.00),
        (3, 0, 10, 25, 75.00),
        (3, 1, 26, 50, 35.00),
        (3, 0, 26, 50, 45.00),
        (3, 1, 51, 9999, 0.00),
        (3, 0, 51, 9999, 0.00),
        (1, 1, 1, 10, 6.00),
        (1, 1, 11, 9999, 3.00),
        (1, 0, 1, 10, 7.00),
        (1, 0, 11, 9999, 4.00),
        (2, 1, 1, 9999, 8.00),
        (2, 0, 1, 9999, 8.00)
        
-- 订单明细表
CREATE TABLE #OrderItems 
(
    id              int IDENTITY(1,1),
    [OrderId]       int NOT NULL,
    RecipientId     int NOT NULL,
    Reciprocated    bit NOT NULL,
    MemberTypeId    INT NOT NULL,
    CONSTRAINT [PK_#OrderItem] 
        PRIMARY KEY ([OrderId], RecipientId)
);

-- 示例数据
INSERT INTO #OrderItems (OrderId, RecipientId, Reciprocated, MemberTypeId)
VALUES (129, 4, 0, 1),
        (129, 8, 0, 1),
        (129, 13, 0, 1),
        (129, 23, 0, 1),
        (129, 30, 0, 1),
        (129, 31, 0, 1),
        (129, 39, 0, 1),
        (129, 41, 0, 1),
        (129, 42, 0, 1),
        (129, 45, 0, 1),
        (129, 55, 0, 1),
        (129, 59, 0, 1),
        (129, 60, 1, 1),
        (129, 71, 0, 1),
        (129, 72, 0, 1),
        (129, 73, 0, 1),
        (129, 77, 0, 1),
        (129, 87, 0, 1),
        (129, 92, 0, 1),
        (129, 96, 0, 1),
        (129, 100, 0, 1),
        (129, 110, 0, 1),
        (129, 111, 0, 1),
        (129, 120, 0, 1),
        (129, 123, 0, 1),
        (129, 129, 0, 1),
        (129, 134, 0, 1),
        (129, 137, 1, 1 ),
        (129, 139, 0, 1),
        (129, 142, 1, 1),
        (129, 153, 0, 1),
        (129, 158, 0, 1),
        (129, 163, 0, 1),
        (129, 170, 0, 1),
        (129, 173, 0, 1),
        (129, 178, 1, 1),
        (129, 186, 0, 1),
        (129, 195, 0, 1),
        (129, 208, 0, 1),
        (129, 216, 0, 1),
        (129, 217, 0, 1),
        (129, 223, 0, 1),
        (129, 229, 0, 1),
        (129, 236, 0, 1),
        (129, 244, 0, 1),
        (129, 256, 0, 1),
        (129, 270, 0, 1),
        (129, 272, 1, 1),
        (129, 282, 0, 1),
        (129, 298, 0, 1),
        (129, 311, 0, 1),
        (129, 321, 0, 1),
        (129, 323, 0, 1),
        (129, 327, 0, 1),
        (129, 329, 0, 1),
        (129, 330, 0, 1),
        (129, 344, 1, 1),
        (129, 366, 0, 2),
        (129, 368, 0, 1),
        (129, 372, 0, 1),
        (129, 410, 0, 1),
        (129, 412, 0, 1),
        (129, 431, 0, 1),
        (129, 451, 0, 1),
        (129, 546, 0, 1),
        (129, 552, 0, 1),
        (129, 612, 1, 1),
        (129, 741, 0, 1),
        (129, 748, 0, 2),
        (129, 749, 1, 1),
        (129, 750, 1, 1),
        (129, 756, 1, 1),
        (129, 778, 0, 1),
        (129, 781, 1, 1),
        (129, 810, 0, 1),
        (129, 822, 1, 1),
        (129, 867, 0, 1),
        (129, 873, 0, 1),
        (129, 901, 0, 1),
        (129, 955, 0, 2),
        (129, 983, 0, 1),
        (129, 1034, 1, 1),
        (129, 1060, 0, 1)

二、目标计算结果 #

当订单ID=129、早鸟用户（@EarlyBird=1）、启用互惠规则（@Reciprocate=1）时，需生成如下计费明细：

三、计算规则 #

• 忽略#OrderItems中Reciprocated = 1的记录；
• 通过#MemberTypeChargeType关联会员类型与计费类型；
• 计费规则：
  • isAGroupRate=0：按对应会员类型的有效人数分阶梯计费；
  • isAGroupRate=1：按所有有效订单记录的总人数匹配区间计费（仅当@Reciprocate=1时生效）。

四、现有实现问题 #

当前代码存在硬编码问题，直接指定了ChargeTypeId=1/2/3和MemberTypeId=1/2，无法适配后续会员类型或计费类型的扩展需求。

五、优化后的存储过程 #

CREATE PROCEDURE CalculateOrderTotal
    @OrderId INT,
    @EarlyBird BIT,
    @Reciprocate BIT,
    @Debug BIT = 0
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 计算各会员类型的有效人数，以及总有效人数
    DECLARE @MemberCounts TABLE (MemberTypeId INT, ItemCount INT);
    INSERT INTO @MemberCounts (MemberTypeId, ItemCount)
    SELECT MemberTypeId, COUNT(*) AS ItemCount
    FROM #OrderItems
    WHERE OrderId = @OrderId AND Reciprocated = 0
    GROUP BY MemberTypeId;

    DECLARE @TotalValidCount INT = (SELECT SUM(ItemCount) FROM @MemberCounts);

    IF @Debug = 1
    BEGIN
        SELECT 
            @OrderId AS [@OrderId],
            @EarlyBird AS [@EarlyBird],
            @Reciprocate AS [@Reciprocate],
            @TotalValidCount AS [@TotalValidCount];
        SELECT * FROM @MemberCounts;
    END

    -- 2. 关联计费类型与会员类型，计算各阶梯费用
    WITH ChargeCalculations AS (
        -- 处理非组计费（isAGroupRate=0）
        SELECT
            c.Id AS ChargeTypeId,
            cr.FromCount,
            cr.ToCount,
            mc.ItemCount,
            cr.RatePrice,
            -- 计算当前阶梯的计费数量：取区间上限和剩余人数的较小值
            CASE 
                WHEN mc.ItemCount <= cr.FromCount - 1 THEN 0
                WHEN mc.ItemCount <= cr.ToCount THEN mc.ItemCount - (cr.FromCount - 1)
                ELSE cr.ToCount - (cr.FromCount - 1)
            END * cr.RatePrice AS Total
        FROM #ChargeTypes c
        JOIN #MemberTypeChargeType mct ON c.Id = mct.ChargeTypeId
        JOIN @MemberCounts mc ON mct.MemberTypeId = mc.MemberTypeId
        JOIN #ChargeRates cr ON c.Id = cr.ChargeTypeId
        WHERE c.isAGroupRate = 0 AND cr.EarlyBird = @EarlyBird
        UNION ALL
        -- 处理组计费（isAGroupRate=1），仅当启用互惠规则时生效
        SELECT
            c.Id AS ChargeTypeId,
            cr.FromCount,
            cr.ToCount,
            @TotalValidCount AS ItemCount,
            cr.RatePrice,
            CASE 
                WHEN @Reciprocate = 1 AND @TotalValidCount BETWEEN cr.FromCount AND cr.ToCount THEN cr.RatePrice
                ELSE 0
            END AS Total
        FROM #ChargeTypes c
        JOIN #ChargeRates cr ON c.Id = cr.ChargeTypeId
        WHERE c.isAGroupRate = 1 AND cr.EarlyBird = @EarlyBird
        -- 确保组计费仅关联对应的会员类型（通过MemberTypeChargeType过滤）
        AND EXISTS (
            SELECT 1 FROM #MemberTypeChargeType mct 
            WHERE mct.ChargeTypeId = c.Id
            AND EXISTS (SELECT 1 FROM @MemberCounts mc WHERE mc.MemberTypeId = mct.MemberTypeId)
        )
    )
    -- 输出计费明细及总计
    SELECT 
        ChargeTypeId,
        FromCount,
        ToCount,
        ItemCount,
        RatePrice,
        Total
    FROM ChargeCalculations
    WHERE Total > 0 OR (RatePrice = 0 AND ItemCount > 0) -- 保留免费计费的记录
    ORDER BY ChargeTypeId, FromCount;

    -- 输出订单总计
    SELECT SUM(Total) AS OrderTotal FROM ChargeCalculations;
END

存储过程调用示例 #

-- 测试调用：订单129，早鸟用户，启用互惠规则，开启调试
EXEC CalculateOrderTotal @OrderId=129, @EarlyBird=1, @Reciprocate=1, @Debug=1;

内容的提问来源于stack exchange，提问作者Chana T