Sql Server迭代
问题描述:
我正在使用while
循环来检索5个值。Sql Server迭代
在循环结束时,我想将所有值加起来以产生总和。
例如
- ITERATION_1 = 5
- ITERATION_2 = 8
- ITERATION_3 = 2
- ITERATION_4 = 3
- ITERATION_5 = 2
所以总将是20
这是我到目前为止:
WHILE(@PROJECT_SEQ>0)
BEGIN
SELECT @PROJ_ID=PROJECT_ID FROM #TmpProjects WHERE [email protected]_SEQ
SELECT COUNT(idDP_Entity_Activity)AS COUNT1 FROM DP_Project_Details PD
INNER JOIN DP_Initiation I
ON I.FK_Project_ID=PD.Project_ID
INNER JOIN DP_Entities E
ON E.FK_Project_ID=PD.Project_ID
INNER JOIN DP_Entity_Activity EA
ON EA.FK_idDP_Entities=E.idDP_Entities
WHERE [email protected]_ID
--SET @[email protected][email protected]
--SELECT @COUNT,@PROJ_ID
SET @[email protected]_SEQ-1
END
答
当然,更简单的方法是将这些项目计算在内?
SELECT COUNT(idDP_Entity_Activity)AS [TOTAL_PROJECT_COUNT]
FROM DP_Project_Details PD
INNER JOIN DP_Initiation I
ON I.FK_Project_ID=PD.Project_ID
INNER JOIN DP_Entities E
ON E.FK_Project_ID=PD.Project_ID
INNER JOIN DP_Entity_Activity EA
ON EA.FK_idDP_Entities=E.idDP_Entities
WHERE PD.Project_ID in (Select Project_ID From #TmpProjects
Where Seq_No <= @PROJECT_SEQ)
+0
@巴里......谢谢你,这个逻辑没有点击我的脑海。我以一种完全不同的方式思考。非常感谢!!!! – Puskar 2013-03-15 08:53:39
你为什么要在循环中做这件事,而不是把它写成单个查询? – 2013-03-15 08:34:53