本文介绍了是否可以使用 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
这篇关于是否可以使用 SQL 将身份添加到 GROUP?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！