SQL 如何在选择查询中递增
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3214544/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to increment in a select query
提问by Mikecancook
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
And yes, I am stuck with SQL2k. Otherwise that row_number() would work.
是的，我坚持使用 SQL2k。否则 row_number() 会起作用。
回答by Bill Karwin
Assuming a table:
假设一个表：
CREATE TABLE [SomeTable] (
[id] INTEGER,
[order] INTEGER,
PRIMARY KEY ([id], [order])
);
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.
在 Microsoft SQL Server 2000 中实现这一点的一种方法是使用子查询来计算具有相同 ID 和较低顺序的行。
SELECT *, (SELECT COUNT(*) FROM [SomeTable] counter
WHERE t.id = counter.id AND t.order < counter.order) AS row_num
FROM [SomeTable] t
Tip:It's 2010. Soon your SQL Server will be old enough to drive.
提示：现在是 2010 年。很快您的 SQL Server 就可以驱动了。
If you use SQL Server 2005 or later, you get wonderful new functions like ROW_NUMBER() OVER (PARTITION...).
如果您使用 SQL Server 2005 或更高版本，您将获得美妙的新功能，例如ROW_NUMBER() OVER (PARTITION...).
回答by Eddie
Yes you want ROW_NUMBER().
是的，你想要 ROW_NUMBER()。
I would try:
我会尝试：
SELECT id, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS Counter
回答by Jamie
One way to do this is to throw the data into a temp table with an identity column that is used as a row number. Then make the counter column a count of the other rows with the same Id and a lower row number + 1.
执行此操作的一种方法是将数据放入临时表中，其中标识列用作行号。然后使计数器列成为具有相同 Id 和较低行号 + 1 的其他行的计数。
CREATE TABLE #MyData(
Id INT
);
INSERT INTO #MyData VALUES(3);
INSERT INTO #MyData VALUES(3);
INSERT INTO #MyData VALUES(3);
INSERT INTO #MyData VALUES(3);
INSERT INTO #MyData VALUES(6);
INSERT INTO #MyData VALUES(6);
INSERT INTO #MyData VALUES(6);
INSERT INTO #MyData VALUES(7);
CREATE TABLE #MyTempTable(
RowNum INT IDENTITY(1,1),
Id INT,
Counter INT
);
INSERT INTO #MyTempTable
SELECT Id, 0
FROM #MyData
ORDER BY Id;
SELECT Id, (SELECT COUNT(*) + 1 FROM #MyTempTable WHERE Id = t1.Id AND RowNum < t1.RowNum) AS 'Counter'
FROM #MyTempTable t1;
You should get the following output based on your example:
您应该根据您的示例获得以下输出：
Id Counter
3 1
3 2
3 3
3 4
6 1
6 2
6 3
7 1
回答by Atish Narlawar
For MySql, I was able to make it with this query.
对于 MySql，我可以通过这个查询来实现。
SELECT (SELECT COUNT(id) +1 FROM sku s WHERE t.item_id = s.item AND s.id < t.sku_id) AS rowNumber, t.*
FROM
(select item.Name as itemName ,item.id as item_id , sku.Name as skuName ,sku.id as sku_id from item
INNER JOIN sku ON item.id = sku.item
WHERE item.active = 'Y'
) t
1 Roasted Pistachios (Salted, In Shell) 84 1 Pound Bags 84
3 Roasted Pistachios (Salted, In Shell) 84 25 Pound Cases 1174
5 Roasted Pistachios (Salted, In Shell) 84 12 x 2.6 Ounce Bags 5807
2 Roasted Pistachios (Salted, In Shell) 84 5 Pound Bags 814
4 Roasted Pistachios (Salted, In Shell) 84 Samples 4724
6 Roasted Pistachios (Salted, In Shell) 84 12 x 3.2 Ounce Bags 18145
4 Star Fruit 981 5 Pound Bags 17462
1 Star Fruit 981 1 Pound Bags 2125
3 Star Fruit 981 11 Pound Bags 2226
2 Star Fruit 981 44 Pound Cases 2156
回答by Philip Kelley
Having row_number() means you have to deal with far, far fewer correlated subqueries. @Bill Karwin's solution works (+1); here's another version that does the same thing but that might be a bit easier to follow. (I used datetimes to determine ordering.)
拥有 row_number() 意味着您必须处理非常少的相关子查询。@Bill Karwin 的解决方案有效（+1）；这是另一个做同样事情的版本，但它可能更容易理解。（我使用日期时间来确定排序。）
-- Test table
CREATE TABLE Test
( Id int not null
,Loaded datetime not null
)
-- Load dummy data with made-up distinct datetimes
INSERT Test values (3, 'Jan 1, 2010')
INSERT Test values (3, 'Jan 2, 2010')
INSERT Test values (3, 'Jan 5, 2010')
INSERT Test values (3, 'Jan 7, 2010')
INSERT Test values (6, 'Feb 1, 2010')
INSERT Test values (6, 'Feb 11, 2010')
INSERT Test values (7, 'Mar 31, 2010')
-- The query
SELECT t1.Id, count(*) Counter
from Test t1
inner join Test t2
on t2.Id = t1.Id
and t2.Loaded <= t1.Loaded
group by t1.Id, t1.Loaded
-- Clean up when done
DROP TABLE Test
It is important to note that, without good indexes (and perhaps even with them), these kinds of queries can perform very poorly, particularly on large tables. Check and optimize carefully!
重要的是要注意，如果没有好的索引（甚至可能有索引），这些类型的查询的性能会很差，尤其是在大型表上。仔细检查和优化！
