将增量SQL添加到相关ID [英] Adding Incremental SQL to relevant IDs

问题描述

您好，我想做的是提取数据，并按相关ID进行分组，然后根据ID添加增量，因此对于这些ID行，我想添加1 2 3 4 5 6等.

例如

Hi there, what I am trying to do is pull out data, and group by a relevant ID then add incremental dependant on the IDs, so for each of those ID rows I want to add 1 2 3 4 5 6 etc.

so for example

ID     -           Value
333     -          1
333      -         2
333       -        3
333        -       4
333         -      5

456          -     1
456           -    2
456            -   3

我尝试了几种情况，但都失败了...有什么想法吗?

I have tried several scenarios but all failed... any ideas?

SELECT table1.choice1a, Sum(table1.choice1b) AS SumOchoice1b
FROM table1
GROUP BY table1.choice1a
HAVING (((table1.choice1a) Is Not Null));

推荐答案

具体取决于您的SQL Server版本，您可以执行以下操作:

SQL 2000
对于SQL 2000，我相信应该有一个唯一的ID，以便您能够执行此操作.不知道是否有不需要您具有唯一ID的解决方案.

Depending on the version of your SQL Server, you can do the following:

SQL 2000
For SQL 2000, I believe there should be a unique ID for you to be able to do it. Not sure if there are solutions that does not require you to have a unique ID.

SELECT 
tbl2.PKID, -- this is your unique ID
tbl2.ID, 
(select count(1) from table1 where PKID <= tbl2.PKID and ID = tbl2.ID) AS RowNumber
from table1 tbl2
order by ID, RowNumber

SQL 2005

SQL 2005

SELECT
tbl1.ID,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS RowNumber
from table1 tbl1

如果您使用的是SQL 2005或更高版本，请使用ROW_NUMBER(). 示例:

Use ROW_NUMBER() if you are using SQL 2005 or more..
Example:

SELECT table1.choice1a, table1.choice1b AS SumOchoice1b, row_number() over(partition by table1.choice1b order by table1.choice1b) as myrank 
FROM table1

请查看以下链接以获取示例:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139051 [ ^ ]

check the following link for an example:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139051[^]

这篇关于将增量SQL添加到相关ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！