在sql中生成序列号...具有分组序列 [英] generate sequence No in sql ...with grouping sequence

问题描述

ProductID   Name                   LocationID   Quantity SeqNo
----------- ---------------------- ------------ -------- ----
494         Paint - Silver         3            49       1
495         Paint - Blue           3            49       1
493         Paint - Red            3            51       3
496         Paint - Yellow         3            56       4

这里....使用Rank（）函数seqNo列是生成...但是1 ...后我想要2 ....然后3 ....但它跳过数字....（分组是在LoationID和数量上）。



我想要下面

here....by using Rank() function seqNo column was generated...but after 1....i want 2....and then 3....but it skips the numbers....(grouping is on LoationID and Quantity).

I Want Out Like Below

ProductID   Name                   LocationID   Quantity SeqNo
----------- ---------------------- ------------ -------- ----
494         Paint - Silver         3            49       1
495         Paint - Blue           3            49       1
493         Paint - Red            3            51       2
496         Paint - Yellow         3            56       3

我应该怎么做....

how should i do that....

推荐答案

你可以使用密集排名

[ ^ ]



根据MSDN：



返回结果集分区内的行级别， 排名中没有任何差距。一行的等级是一行加上有关行之前的不同等级的数量。

You can use Dense Rank
[^]

According to MSDN:

Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

SELECT
    ProductID,
    Name,
    LocationID,
    Quantity,
    DENSE_RANK() OVER (ORDER BY LocationID, Quantity ASC) AS Rank
FROM
    Products
ORDER
    BY LocationID, Quantity;

尝试在评论中给出的查询......

快乐编码！

：）

try query given below in comments...
Happy Coding!
:)

这篇关于在sql中生成序列号...具有分组序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！