如何编写SQL查询获取每笔Encashment对应的MoneyAccepted累计金额？

这是个典型的时间维度累计计算需求，我给你提供两种实用的SQL实现方案，适配不同的数据库环境：

方案一：关联子查询（兼容性强） #

这种写法几乎支持所有SQL数据库，逻辑直观易懂：

WITH Encashment AS (
 SELECT T.MachineId, T.Amount, CAST(Occured AS DATETIME) AS Occured
 FROM (VALUES
 (1, 101, '2017-10-20 09:36:40.057')
 ,(1, 203, '2017-10-14 12:36:30.081')
 ,(1, 400, '2017-10-11 04:17:38.023')
 ) AS T(MachineId, Amount, Occured)
), MoneyAccepted AS (
 SELECT T.MachineId, T.Amount, CAST(Occured AS DATETIME) AS Occured
 FROM (VALUES
 (1, 1, '2017-10-15 09:36:40.057')
 ,(1, 100, '2017-10-16 12:36:30.081')
 ,(1, 100, '2017-10-12 16:17:38.023')
 ,(1, 1, '2017-10-13 09:37:47.057')
 ,(1, 1, '2017-10-13 09:37:47.057')
 ,(1, 1, '2017-10-12 15:37:47.057')
 ,(1, 100, '2017-09-15 12:37:31.081')
 ,(1, 100, '2017-09-15 16:37:31.081')
 ,(1, 100, '2017-09-16 13:37:31.081')
 ,(1, 100, '2017-09-17 13:37:31.081')
 ) AS T(MachineId, Amount, Occured)
)
SELECT 
    e.MachineId,
    e.Amount AS EncashmentAmount,
    e.Occured AS EncashmentTime,
    -- 计算当前取现记录之前的所有收款累计金额
    COALESCE((SELECT SUM(ma.Amount) 
              FROM MoneyAccepted ma
              WHERE ma.MachineId = e.MachineId
                AND ma.Occured < e.Occured), 0) AS TotalAcceptedBeforeEncashment
FROM Encashment e
ORDER BY e.Occured DESC;

逻辑说明： #

• 主查询遍历每一条Encashment（取现）记录
• 关联子查询匹配同一机器ID下，发生时间早于当前取现时间的所有MoneyAccepted（收款）记录，求和得到累计金额
• COALESCE函数用于处理“没有前置收款记录”的场景，确保返回0而非NULL

方案二：窗口函数（高效优化） #

如果你的数据库支持窗口函数（比如SQL Server、PostgreSQL、MySQL 8.0+、Oracle等），这种写法性能更优，尤其在数据量大的时候：

WITH Encashment AS (
 SELECT T.MachineId, T.Amount, CAST(Occured AS DATETIME) AS Occured, 'Encashment' AS RecordType
 FROM (VALUES
 (1, 101, '2017-10-20 09:36:40.057')
 ,(1, 203, '2017-10-14 12:36:30.081')
 ,(1, 400, '2017-10-11 04:17:38.023')
 ) AS T(MachineId, Amount, Occured)
), MoneyAccepted AS (
 SELECT T.MachineId, T.Amount, CAST(Occured AS DATETIME) AS Occured, 'MoneyAccepted' AS RecordType
 FROM (VALUES
 (1, 1, '2017-10-15 09:36:40.057')
 ,(1, 100, '2017-10-16 12:36:30.081')
 ,(1, 100, '2017-10-12 16:17:38.023')
 ,(1, 1, '2017-10-13 09:37:47.057')
 ,(1, 1, '2017-10-13 09:37:47.057')
 ,(1, 1, '2017-10-12 15:37:47.057')
 ,(1, 100, '2017-09-15 12:37:31.081')
 ,(1, 100, '2017-09-15 16:37:31.081')
 ,(1, 100, '2017-09-16 13:37:31.081')
 ,(1, 100, '2017-09-17 13:37:31.081')
 ) AS T(MachineId, Amount, Occured)
), CombinedRecords AS (
 SELECT MachineId, Amount, Occured, RecordType FROM Encashment
 UNION ALL
 SELECT MachineId, Amount, Occured, RecordType FROM MoneyAccepted
), RunningTotals AS (
 SELECT 
     *,
     SUM(CASE WHEN RecordType = 'MoneyAccepted' THEN Amount ELSE 0 END) 
         OVER (PARTITION BY MachineId ORDER BY Occured ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS RunningTotal
 FROM CombinedRecords
)
SELECT 
    MachineId,
    Amount AS EncashmentAmount,
    Occured AS EncashmentTime,
    COALESCE(RunningTotal, 0) AS TotalAcceptedBeforeEncashment
FROM RunningTotals
WHERE RecordType = 'Encashment'
ORDER BY Occured DESC;

逻辑说明： #

1. 给两个表添加RecordType标识，区分取现和收款记录
2. 将两个表的数据合并为一个数据集CombinedRecords
3. 使用窗口函数SUM() OVER()，按机器ID分区、时间排序，计算到当前记录前一条的所有收款金额累计（ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING限定了累计范围）
4. 最后筛选出所有取现记录，得到对应的前置累计收款金额

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