是否可以使用 SQL 将身份添加到 GROUP?

问题描述

是否可以将标识列添加到 GROUP BY 以便每个重复项都有一个标识号?

Is it possible to add a identity column to a GROUP BY so that each duplicate has a identity number?

我的原始数据是这样的:

My original data looks like this:

1    AAA  [timestamp]
2    AAA  [timestamp]
3    BBB  [timestamp]
4    CCC  [timestamp]
5    CCC  [timestamp]
6    CCC  [timestamp]
7    DDD  [timestamp]
8    DDD  [timestamp]
9    EEE  [timestamp]
....

我想将其转换为:

1    AAA   1
2    AAA   2
4    CCC   1
5    CCC   2
6    CCC   3
7    DDD   1
8    DDD   2
...

解决方案是:

CREATE PROCEDURE [dbo].[RankIt]
AS
BEGIN
SET NOCOUNT ON;

SELECT  *, RANK() OVER(PARTITION BY col2 ORDER BY timestamp DESC) AS ranking 
FROM MYTABLE;

END

推荐答案

您可以尝试使用 ROW_NUMBER 如果您使用的是 Sql Server 2005

You could try using ROW_NUMBER if you are using Sql Server 2005

DECLARE @Table TABLE(
        ID INT,
        Val VARCHAR(10)
)

INSERT INTO @Table SELECT 1,'AAA'
INSERT INTO @Table SELECT 2,'AAA'
INSERT INTO @Table SELECT 3,'BBB' 
INSERT INTO @Table SELECT 4,'CCC' 
INSERT INTO @Table SELECT 5,'CCC' 
INSERT INTO @Table SELECT 6,'CCC' 
INSERT INTO @Table SELECT 7,'DDD' 
INSERT INTO @Table SELECT 8,'DDD' 
INSERT INTO @Table SELECT 9,'EEE' 

SELECT  *,
        ROW_NUMBER() OVER(PARTITION BY VAL ORDER BY Val)
FROM    @Table