相当于 "ROWS BETWEEN UNBOUNDED PRECEDING" 的 SQL Server 2008

Question

我的DB 有这个查询

WITH cte AS (
SELECT
r.CustomerID,
r.DateReceived,
r.Point,
mr.MaxPoint,
COALESCE(SUM(r.Point) OVER (PARTITION BY r.CustomerID ORDER BY r.DateReceived
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS cumPoints
FROM Rewards r
INNER JOIN MaxRewards mr ON r.CustomerID = mr.CustomerID
)
SELECT
CustomerID,
DateReceived,
CASE WHEN cumPoints + Point < MaxPoint
THEN Point
ELSE CASE WHEN MaxPoint - cumPoints > 0
THEN MaxPoint - cumPoints ELSE 0 END END AS Point
FROM cte
ORDER BY
CustomerID,
DateReceived;

当我将其部署到另一台仍在使用SQL 2008的机器上时，查询ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING不起作用，有人知道SQL 2008的等效语法吗？

Answer 1

您肯定应该升级您的SQL Server。同时，您可以使用相关的子查询：

SELECT r.CustomerID, r.DateReceived, r.Point, mr.MaxPoint,
(SELECT SUM(r2.Point)
FROM rewards r2
WHERE r2.CustomerId = r.CustomerId AND
r2.DateReceived < r.DateReceived
) AS cumPoints
FROM Rewards r JOIN
MaxRewards mr
ON r.CustomerID = mr.CustomerID

这也可以表示为使用cross apply的横向连接。

这假设DateReceived不具有重复项。如果这是可能的，那么这是微妙的不同。

还要注意，性能可能比使用窗口函数差得多，但rewards(customerid, datereceived, point)上的索引会有所帮助。