sql - 查询以显示交易表中的已用积分
问题描述
我正在使用一个包含信用交易的表格,我想在其中显示在进行销售时花费了谁的信用。
在表中:
Credits
由实体使用唯一的实体代码添加(记录在列中GivenByUserCode
)- 信用添加总是有这样的代码。
- 花费的积分将始终具有负值。
- 已花费的积分将没有实体代码(值为
GivenByUserCode
isnull
)。
以上述数据为例,如果用户2018-01-02
在报告上进行购买,则应显示所有这些信用来自BM01
。增加的复杂性在于,一次购买可以拆分为多个添加,请参阅购买2018-02-03
分为 3 个添加。
我认为该解决方案与使用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 已“用完”)
- 对于在信用日志 id 7 中花费 40 信用可以追溯到
- 从信用记录 id 2 中添加的剩余部分,5 个信用
- 信用记录 ID 5(添加 5)
- 信用记录 id 6(添加 40 剩余 10)
- 对于信用记录 8 中的 4 个信用的支出,使用信用记录 id 6 的余额
请注意,剩余的总余额为 6 个积分,余额不必归零,但永远不会为负数,因为用户只能花费他们所拥有的。
解决方案
尝试这个:
WITH Credits_added AS (
SELECT CreditLogId, OccurredOn, credits
, SUM(credits) OVER (ORDER BY CreditLogId) - credits AS b --before
, SUM(credits) OVER (ORDER BY CreditLogId) AS a --after
, GivenByUserCode
FROM @CreditLogs
WHERE Credits > 0)
, Credits_spent AS (
SELECT CreditLogId, OccurredOn, credits
, SUM(credits) OVER (ORDER BY CreditLogId) * -1 + credits AS b
, SUM(credits) OVER (ORDER BY CreditLogId) * -1 AS a
FROM @CreditLogs
WHERE Credits < 0)
SELECT s.CreditLogId, s.OccurredOn
, CASE WHEN a.a > s.a THEN s.a ELSE a.a END - CASE WHEN a.b > s.b THEN a.b ELSE s.b END AS Credits
, a.GivenByUserCode
FROM Credits_added AS a
INNER JOIN Credits_spent AS s ON a.a > s.b AND s.a > a.b
推荐阅读
- vb.net - 如何获取 UltraComboEditor ValueList 项的索引?
- javascript - AJAX PHP 空白 POST 数据已发送 Godaddy
- python - 带有请求库的 Roboframework CDATA xml
- vue.js - Axios 拦截器未从 vuex 商店获取当前用户身份验证令牌
- c - 使用 Keil 从 RAM 内存执行库函数
- r - 在 R 中外推人口普查数据
- hadoop - 推测执行如何影响 s3-dist-cp 作业?
- c# - 如何根据方法参数从 TEntity 中选择和返回自定义属性
- python-3.x - mssql OpenJson 函数的 SQLAlchemy 会话查询
- firebase - 使用 Firebase 云功能从客户端 Google 登录获取 Bearer 令牌