如何在选择查询中递增 [英] How to increment in a select query

问题描述

我有一个正在处理的查询，我想增加其中一个字段并在键值不同时重新启动计数器.

I've got a query I'm working on and I want to increment one of the fields and restart the counter when a key value is different.

我知道此代码不起作用.以编程方式这就是我想要的...

I know this code doesn't work. Programmatically this is what I want...

declare @counter int, @id
set @counter = 0
set @id = 0

select distinct 
  id, 
  counter = when id = @id 
              then @counter += 1
            else @id = id  
               @counter = 1     

...最终结果看起来像这样:

...with the end result looking something like this:

ID    Counter
3     1
3     2 
3     3
3     4
6     1
6     2
6     3
7     1

是的，我坚持使用 SQL2k.否则 row_number() 会起作用.

And yes, I am stuck with SQL2k. Otherwise that row_number() would work.

推荐答案

假设一个表格:

CREATE TABLE [SomeTable] (
  [id] INTEGER,
  [order] INTEGER,
  PRIMARY KEY ([id], [order])
);

在 Microsoft SQL Server 2000 中实现这一点的一种方法是使用子查询来计算具有相同 ID 和较低顺序的行.

One way to get this in Microsoft SQL Server 2000 is to use a subquery to count the rows with the same id and a lower ordering.

SELECT *, (SELECT COUNT(*) FROM [SomeTable] counter 
           WHERE t.id = counter.id AND t.order < counter.order) AS row_num
FROM [SomeTable] t

提示:现在是 2010 年.很快您的 SQL Server 就可以驱动了.

Tip: It's 2010. Soon your SQL Server will be old enough to drive.

如果您使用 SQL Server 2005 或更高版本，您将获得很棒的新函数，例如 ROW_NUMBER() OVER (PARTITION...).

If you use SQL Server 2005 or later, you get wonderful new functions like ROW_NUMBER() OVER (PARTITION...).

这篇关于如何在选择查询中递增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！