在sql中生成序列号...具有分组序列

本文介绍了在sql中生成序列号...具有分组序列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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! :)