如何在特定列中找到具有相同数据的连续行的数量...... [英] How to Find the no of Consecutive Rows Having Same Data in a Particular Column......

问题描述

大家好....



我有一个要求在Sql Server的'n'连续行中添加列的值.... Am无法在查询中执行此操作......



要求是这样的......让我们说Table_Y我有...... < br $>

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     10
2     X       OrderCV     20
3     X       OrderAX     100
.     .          .         .
.     .          .         .
n     X       OrderX      300 --Calculate The Sum Untill Here (10+20+100+...+300)
n+1   Y       OrderA      15
n+2   Y       OrderB      23
n+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...

Thanks
Raj

推荐答案

嗨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  10

If you want to get a result set like the following,

     ITEM     QTY
   -----------------
     X    60
     Y    50
     X    90
     Z   100
     Y    17
     X    10

then 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 ORDERQTYCTE
GROUP 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

这篇关于如何在特定列中找到具有相同数据的连续行的数量......的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！