在T-SQL中运行SUM

小编典典

在T-SQL中运行SUM

sql

对不起，不好的话题，但我不确定该怎么称呼。

我有一张看起来像这样的桌子：

+-----++-----+
| Id  ||Count|
+-----++-----+
| 1   || 1   |
+-----++-----+
| 2   || 5   |
+-----++-----+
| 3   || 8   |
+-----++-----+
| 4   || 3   |
+-----++-----+
| 5   || 6   |
+-----++-----+
| 6   || 8   |
+-----++-----+
| 7   || 3   |
+-----++-----+
| 8   || 1   |
+-----++-----+

我试图从此表中进行选择，其中每当row1 + row2 + row3（等等）的总和达到10时，它就是一个“ HIT”，并且计数重新开始。

要求的输出：

+-----++-----++-----+
| Id  ||Count|| HIT |
+-----++-----++-----+
| 1   || 1   ||  N  | Count = 1
+-----++-----++-----+
| 2   || 5   ||  N  | Count = 6
+-----++-----++-----+
| 3   || 8   ||  Y  | Count = 14 (over 10)
+-----++-----++-----+
| 4   || 3   ||  N  | Count = 3
+-----++-----++-----+
| 5   || 6   ||  N  | Count = 9
+-----++-----++-----+
| 6   || 8   ||  Y  | Count = 17 (over 10..)
+-----++-----++-----+
| 7   || 3   ||  N  | Count = 3
+-----++-----++-----+
| 8   || 1   ||  N  | Count = 4
+-----++-----++-----+

如何做到这一点，并获得最佳性能？我不知道..

阅读 164

2021-05-23

共1个答案

小编典典

您可以使用递归查询

请注意以下查询，假设id值均按顺序排列，否则，请用于ROW_NUMBER()创建新的id

WITH cte AS (
  SELECT id, [Count], [Count] AS total_count
  FROM Table1 
  WHERE id = 1
  UNION ALL
  SELECT t2.id,t2.[Count], CASE WHEN t1.total_count >= 10 THEN t2.[Count] ELSE t1.total_count + t2.[Count] END
  FROM Table1 t2
  INNER JOIN cte t1 
    ON t2.id = t1.id + 1
  )
SELECT *
FROM cte
ORDER BY id

sqlfiddle

2021-05-23