查询以显示事务性表中已使用的学分。

Question

我正在使用一个包含信用交易的表，在该表中，我希望显示谁的信用是在销售时使用的。

在表中：

• Credits由使用唯一实体代码的实体添加(记录在GivenByUserCode列中)
• 信用加成总是有这样的代码。
• 花掉的学分总是有负值的。
• 花费的学分将没有实体代码( GivenByUserCode的值为null)。

使用上述数据作为示例，如果用户在2018-01-02上进行购买，则报告应显示源自BM01的所有这些信用。add的复杂之处在于，购买可以被拆分为多个添加，参见在2018-02-03上的购买，它被分成三个加法。

我认为这个解决方案将与使用cte和结束有关，但我没有使用这些方法的经验。我在SqlServerCentral上确实发现了一个类似(不一样)的问题。

如有任何帮助/指导，将不胜感激。

输入和DDL

DECLARE @CreditLogs TABLE(CreditLogId int not null identity(1,1), Credits INT NOT NULL, OccurredOn DATETIME2(7) NOT NULL, GivenByUserCode VARCHAR(100) NULL)

INSERT INTO @CreditLogs (Credits, OccurredOn, GivenByUserCode) VALUES
  (10,  '2018-01-01', 'BM01')
, (10,  '2018-01-01', 'BM01')
, (-10, '2018-01-02', NULL)
, (-5,  '2018-01-04', NULL)
, (5,   '2018-02-01', 'SP99')
, (40,  '2018-02-02', 'BM02')
, (-40, '2018-02-03', NULL)
, (-4,  '2018-03-05', NULL)

以表格形式输入

CreditLogId | Credits | OccurredOn | GivenByUserCode
------------+---------+------------+----------------
          1 |      10 | 2018-01-01 |            BM01
          2 |      10 | 2018-01-01 |            BM01
          3 |     -10 | 2018-01-02 |            NULL
          4 |      -5 | 2018-01-04 |            NULL
          5 |       5 | 2018-02-01 |            SP99
          6 |      40 | 2018-02-02 |            BM02
          7 |     -40 | 2018-02-03 |            NULL
          8 |      -4 | 2018-03-05 |            NULL

预期产出

SELECT *
FROM (VALUES
     (3, '2018-01-02', 10, 'BM01')
    ,(4, '2018-01-04', 5, 'BM01')
    ,(7, '2018-02-03', 5, 'BM01')
    ,(7, '2018-02-03', 5, 'SP99')
    ,(7, '2018-02-03', 30, 'BM02')
    ,(8, '2018-03-05', 4, 'BM02')
) expectedOut (CreditLogId, OccurredOn, Credits, GivenByUserCode)

产生输出

CreditLogId | Occurred on | Credits | GivenByUserCode
------------+-------------+---------+----------------
          3 |  2018-01-02 |      10 |            BM01
          4 |  2018-01-04 |       5 |            BM01
          7 |  2018-02-03 |       5 |            BM01
          7 |  2018-02-03 |       5 |            SP99
          7 |  2018-02-03 |      30 |            BM02
          8 |  2018-03-05 |       4 |            BM02

目前为止的代码

不多，我也不知道从这里往哪里走。

WITH totals AS (
    SELECT CreditLogId, OccurredOn, credits, sum(credits) OVER(ORDER BY OccurredOn) AS TotalSpent
    FROM @CreditLogs
    WHERE Credits < 0
)
SELECT *
FROM totals

补充澄清

预期的产出是那些信贷来源的每一笔支出的信贷额。学分用在先进先出(FIFO)的基础上。这里对示例输出中的每个值进行了解释，希望这能澄清所需的输出。

• 对于10个学分的支出(信用日志id 3)可以追溯到从信用日志id 1中增加的部分。
• 对于5个学分的支出(信用日志id 4)可以追溯到从信用日志id 2(因为信用日志id 1被“用完”)中添加的内容。
• 在信用日志中花费40个学分的id 7可以追溯到。
  • 从信贷日志中增加的余额id 2，5学分
  • 信用日志id 5(加5)
  • 信用日志id 6(增加40 so 10剩余)

​

• 对于信贷日志8中的4个学分的支出，使用了信贷日志id 6的余额。

注意，6学分的总余额仍然存在，余额不必为零，但永远不会为负数，因为用户只能使用他们所拥有的。

Answer 1

由于您提到每年将有4到500万条记录，即使这可以通过查询完成，这也是一个缓慢的查询。

我建议有另一个表，如creditSpent，其中包含(PurchaseCreditLogId , additionCreditLogId, Amount)

在插入购买时，查找所有记录，计算每个记录中应减少的金额，并将该信息存储在该表中。

然后，在运行报表时，可以对此表执行简单的查询。