22,207
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[data] INT
,[customerid] VARCHAR(10)
,[revenue] INT
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'20180101',N'A001',N'100')
INSERT INTO dbo.[t] VALUES(N'20180101',N'A002',N'100')
INSERT INTO dbo.[t] VALUES(N'20180102',N'A001',N'200')
INSERT INTO dbo.[t] VALUES(N'20180104',N'A203',N'300')
---- 以上为测试数据 ----
--1. 增加一列 data_num
ALTER TABLE t ADD data_num INT
GO
--2. 更新新增的列 data_num
;WITH cte AS (
--如果不希望相同的并列, DENSE_RANK =》 row_number 即可
SELECT DENSE_RANK() OVER (ORDER BY [data] DESC) AS rid,* FROM t
)
UPDATE cte SET data_num=rid
--3. 查询结果
SELECT * FROM t;
/*
data customerid revenue data_num
----------- ---------- ----------- -----------
20180101 A001 100 3
20180101 A002 100 3
20180102 A001 200 2
20180104 A203 300 1
*/