问题描述
是否可以将标识列添加到 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