sql - 这个问题有基于集合的解决方案吗？

Question

我们有一个表设置如下:

|ID|EmployeeID|Date     |Category       |Hours|
|1 |1         |1/1/2010 |Vacation Earned|2.0  |
|2 |2         |2/12/2010|Vacation Earned|3.0  |
|3 |1         |2/4/2010 |Vacation Used  |1.0  |
|4 |2         |5/18/2010|Vacation Earned|2.0  |
|5 |2         |7/23/2010|Vacation Used  |4.0  |

业务规则如下:

假期余额的计算方法是获得的假期减去使用的假期。

已使用的假期始终首先应用于最早的假期赚取金额。

我们需要返回没有被使用的假期抵消的 Vacation Earned 的行。如果使用的假期仅抵消了获得的假期记录的一部分，我们需要返回显示差异的记录。例如，使用上表，结果集将如下所示:

|ID|EmployeeID|Date     |Category       |Hours|
|1 |1         |1/1/2010 |Vacation Earned|1.0  |
|4 |2         |5/18/2010|Vacation Earned|1.0  |

请注意，记录 2 被删除，因为它被使用时间完全抵消，但记录 1 和 4 仅部分使用，因此它们被计算并返回。

我们想到的唯一方法是在临时表中获取所有获得的假期记录。然后，获取使用的总休假并循环遍历临时表，删除最旧的记录并从使用的总休假中减去该值，直到使用的总休假为零。我们可以清理它，因为剩余的假期使用时间只是最早获得的假期记录的一部分。这将使我们只剩下出色的假期获得记录。

这有效，但效率非常低且性能不佳。此外，随着添加的记录越来越多，性能只会随着时间的推移而降低。

有没有更好的解决方案的建议，基于更好的集合？如果没有，我们就只能这样了。

编辑:这是一个供应商数据库。我们不能以任何方式修改表结构。

Answer 1

在思考这个问题时，我突然想到，您需要关心何时获得假期的唯一原因是它是否到期。如果是这种情况，最简单的解决方案是将“假期过期”记录添加到表中，这样员工的剩余假期量始终只是 sum(vacation earned) - (sum(vacation expired) + sum(vacatation used))。 .您甚至可以通过使用最后一个假期过期记录作为查询的起点来显示您想要的确切记录。

但我猜这不是一个选择。要解决所问的问题，请记住，每当您发现自己使用临时表时，请尝试将该数据放入 CTE(公用表表达式)中。不幸的是，我现在有一个 session ，所以我没有时间编写查询(也许稍后，这听起来很有趣)，但这应该会让你开始。