如何在特定列中找到具有相同数据的连续行的数量......

本文介绍了如何在特定列中找到具有相同数据的连续行的数量......的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 大家好.... 我有一个要求在Sql Server的'n'连续行中添加列的值.... Am无法在查询中执行此操作...... 要求是这样的......让我们说Table_Y我有...... Hi All....I have a Requirement to Add the Value of a Column in 'n' Consecutive Rows in Sql Server.... Am Unable to do that in a Query......The Requirement is like this.... Let's Say Table_Y I have...... Item OrderNo Qty ----- -------- -----1 X OrderZx 102 X OrderCV 203 X OrderAX 100. . . .. . . .n X OrderX 300 --Calculate The Sum Untill Here (10+20+100+...+300)n+1 Y OrderA 15n+2 Y OrderB 23n+3 X OrderC 10 这里的任务是我必须计算X项目的数量总和，直到订单X（即从OrderZx（1）到OrderX（ n）项目X的所有连续订单..）我使用While循环做了...但是尝试以不在循环或光标中的查询的形式进行....我将动态获取此表数据...所以不知道有多少连续的行将具有相同的项目...我认为它可以使用Row_Number（）实现但不确定.... 请注意：输出要求是：10 + 20 + 100 + ... + 300（ieOrderZx（数量）+ OrderCV（数量）+ OrderAX（数量）+ ... = OrderX（数量）OrderX不应包含在输出中...... 任何帮助都会被赞赏... 谢谢 Raj The Task Here is I Have to Calculate the Sum of Qty For the X Item Untill OrderX(i.e. From OrderZx (1) to OrderX (n) All The Continous Orders For Item X).. I did it using While loop... But trying to do in d form of a query not in a loop or Cursor.... I Will get this Table Data on the fly... So Don't Know how Many Consecutive rows Will Have Same Item... i am thinking it can be acheived using Row_Number() But not Sure....Please Note: The Output Required is: 10+20+100+...+300 (i.e.OrderZx(Qty)+OrderCV(Qty)+OrderAX(Qty)+...=OrderX(Qty) OrderX Should not be Included in Output...Any Help Will be Appreciated...ThanksRaj推荐答案 嗨Raja Shekar， Hi Raja Shekar, I assume you have a table Orders with following data. ITEM ORDERNO QTY -------------------- X ORdx 10 X Ordy 20 X Ordx 30 Y Ordx 20 Y Ordy 30 X OrdX 40 X OrdZ 50 Z ORdZ 100 Y Ordzy 8 Y OrdXy 9 X Ordxys 10If you want to get a result set like the following, ITEM QTY ----------------- X 60 Y 50 X 90 Z 100 Y 17 X 10then try the following query on table Orders WITH OrderCTE As ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) RowNum, ITEM,QTY FROM ORDERS),ORDERQTYCTE AS( SELECT TOP 1 0 AS RowNumber,ITEM as Item,CAST(0 as Real) as Qty, 0 OrderLevel FROM OrderCTE UNION ALL SELECT RowNumber+1 AS RowNumber, OrderCTE.ITEM As Item, (CASE WHEN OrderCTE.ITEM = ORDERQTYCTE.Item THEN ORDERQTYCTE.QTY+OrderCTE.QTY ELSE OrderCTE.QTY END) AS Qty, CASE WHEN OrderCTE.ITEM <> ORDERQTYCTE.Item THEN OrderLevel+1 ELSE OrderLevel END As OrderLevel FROM OrderCTE INNER JOIN ORDERQTYCTE ON ORDERQTYCTE.RowNumber = OrderCTE.RowNum - 1 ) SELECT Item,MAX(QTY) QTY FROM ORDERQTYCTEGROUP BY OrderLevel,Item ORDER BY OrderLevel 这里使用CTE，递归CTE来获得所需的结果集。 我希望这个解决方案可以帮到你。快乐的编码。 ：）Here have used CTE, Recursive CTE to get the desired result set. I hope this solution helps you. Happy coding. :) 修改后由OP（Raja Sekhar S）添加到解决方案4的评论： Revised after comment added to Solution 4 by OP (Raja Sekhar S):SELECT SumKey,Item,Sum(Qty) FROM table_y GROUP BY SumKey,Item ORDER BY SumKey,Item 这篇关于如何在特定列中找到具有相同数据的连续行的数量......的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！