求每个客户订单之前的累计订单金额

1.订单表数据如下：

在此基础上增加一列，求每个客户订单之前的累计订单金额，实现效果如下：

方法一（通用版本）：

-- 从订单表中检索客户信息，并计算每个订单之前的订单金额合计  
SELECT  
    t1.客户代码,    -- 选择当前订单的客户代码  
    t1.客户名称,    -- 选择当前订单的客户名称  
    t1.订单日期,    -- 选择当前订单的订单日期  
    t1.订单编号,    -- 选择当前订单的订单编号  
    t1.订单金额,    -- 选择当前订单的订单金额  
    -- 使用子查询计算并返回当前订单之前的所有订单的总金额（针对同一客户）  
    -- 如果没有之前的订单，则使用ISNULL函数将NULL替换为0  
    ISNULL((SELECT SUM(t2.订单金额)  
            FROM 订单 t2  
            WHERE t2.客户代码 = t1.客户代码  -- 只考虑与当前订单相同客户的订单  
              AND t2.订单日期 < t1.订单日期), -- 只考虑日期早于当前订单的订单  
           0) AS 之前订单金额合计             -- 如果子查询结果为NULL，则显示为0  
FROM  
    订单 t1 -- 指定主查询的表为订单表，并通过别名t1引用它  




-- 另外一种写法
SELECT  
    t1.客户代码,  
    t1.客户名称,  
    t1.订单日期,  
    t1.订单编号,  
    t1.订单金额,  
    COALESCE(SUM(t2.订单金额), 0) AS 之前订单金额合计  
FROM  
    订单 t1  
    LEFT JOIN (  
        SELECT  
            客户代码,  
            订单日期,  
            订单金额  
        FROM  
            订单  
    ) t2 ON t2.客户代码 = t1.客户代码 AND t2.订单日期 < t1.订单日期  
GROUP BY  
    t1.客户代码,  
    t1.客户名称,  
    t1.订单日期,  
    t1.订单编号,  
    t1.订单金额  
ORDER BY  
    t1.客户代码,  
    t1.订单日期; 

其中子查询部分详解

1. 子查询从同一个表（但在这里通过别名t2引用，以避免与外层查询的t1混淆）中选择订单金额列，但仅限于那些与当前记录（t1）具有相同客户代码且订单日期早于当前记录订单日期的记录。
2. 使用SUM(t2.订单金额)对这些选定的订单金额进行求和，从而得到在当前订单之前的所有订单的总金额。
3. ISNULL(..., 0)函数用于处理子查询返回NULL的情况（例如，当没有之前的订单时）。如果子查询返回NULL，则ISNULL函数将其替换为0。

方法二（窗口函数 适合数据库版本较高）： 

SELECT
    客户代码,
    客户名称,
    订单日期,
    订单编号,
    订单金额,
    ISNULL(SUM(订单金额) OVER (PARTITION BY 客户代码 ORDER BY 订单日期 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS 之前订单金额合计
FROM
    订单;

1. 窗口函数SUM(订单金额) OVER (...)：
   • 这个窗口函数用于计算订单金额的总和。
   • PARTITION BY 客户代码：指定了窗口的分区条件，即按照客户代码来分组。这意味着窗口函数将分别为每个客户的订单进行求和。
   • ORDER BY 订单日期：指定了窗口内行的排序方式，即按照订单日期进行排序。这对于窗口帧（ROWS BETWEEN ...）的定义是必要的。
   • ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING：定义了窗口帧的范围。这里表示从分区（即客户）的第一个订单开始，到当前订单之前的所有订单（包括当前订单的前一个订单，但不包括当前订单本身）。UNBOUNDED PRECEDING表示从分区的第一个行开始，1 PRECEDING表示到当前行的前一行为止。
2. ISNULL(..., 0)：
   • 这是一个条件函数，用于检查SUM(订单金额) OVER (...)的结果是否为NULL。由于窗口帧在第一个订单的行上是空的（因为没有之前的订单），所以SUM()函数会返回NULL。ISNULL函数确保如果计算结果为NULL，则将其替换为0。

 附代码及测试数据

CREATE TABLE [dbo].[订单] (
  [客户代码] nvarchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [客户名称] nvarchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [订单日期] nvarchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [订单编号] nvarchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [订单金额] int  NULL
)
GO

INSERT INTO [dbo].[订单] ([客户代码], [客户名称], [订单日期], [订单编号], [订单金额]) VALUES (N'E001', N'客户1', N'2024-01-02', N'PO000001', N'6000')
GO

INSERT INTO [dbo].[订单] ([客户代码], [客户名称], [订单日期], [订单编号], [订单金额]) VALUES (N'E001', N'客户1', N'2024-01-03', N'PO000002', N'7000')
GO

INSERT INTO [dbo].[订单] ([客户代码], [客户名称], [订单日期], [订单编号], [订单金额]) VALUES (N'E001', N'客户1', N'2024-01-04', N'PO000003', N'500')
GO

INSERT INTO [dbo].[订单] ([客户代码], [客户名称], [订单日期], [订单编号], [订单金额]) VALUES (N'E002', N'客户2', N'2024-02-09', N'PO000004', N'8500')
GO

INSERT INTO [dbo].[订单] ([客户代码], [客户名称], [订单日期], [订单编号], [订单金额]) VALUES (N'E002', N'客户2', N'2024-02-10', N'PO000005', N'10000')
GO

INSERT INTO [dbo].[订单] ([客户代码], [客户名称], [订单日期], [订单编号], [订单金额]) VALUES (N'E003', N'客户3', N'2024-01-02', N'PO000006', N'2500')
GO

INSERT INTO [dbo].[订单] ([客户代码], [客户名称], [订单日期], [订单编号], [订单金额]) VALUES (N'E003', N'客户3', N'2024-01-03', N'PO000007', N'4000')
GO

INSERT INTO [dbo].[订单] ([客户代码], [客户名称], [订单日期], [订单编号], [订单金额]) VALUES (N'E003', N'客户3', N'2024-01-04', N'PO000008', N'10000')
GO

INSERT INTO [dbo].[订单] ([客户代码], [客户名称], [订单日期], [订单编号], [订单金额]) VALUES (N'E003', N'客户3', N'2024-01-05', N'PO000009', N'3500')
GO