11,849
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('tMember') IS NOT NULL DROP TABLE tMember
GO
CREATE TABLE tMember(
mID INT PRIMARY KEY,
mName NVARCHAR(10),
k1 INT,
k2 INT
)
SET NOCOUNT ON
INSERT INTO tMember(mID,mName,k1) VALUES(1,'小明',80)
INSERT INTO tMember(mID,mName,k1) VALUES(2,'小华',80)
INSERT INTO tMember(mID,mName,k1) VALUES(3,'小刚',95)
INSERT INTO tMember(mID,mName,k1) VALUES(4,'小李',90)
------------ 以上为测试表及测试数据 ---------------
--方法1
;WITH cte AS (
Select Dense_Rank() Over (Order By k1 desc) AS rid,* From tMember
)
UPDATE cte SET k2=rid
--方法2
UPDATE t
SET k2=rid
FROM
(
Select Dense_Rank() Over (Order By k1 desc) AS rid,* From tMember
) AS t INNER JOIN tMember AS tm ON t.mID=tm.mID
--查看更新结果
SELECT * FROM tMember AS tm ORDER BY tm.k2
/*
mID mName k1 k2
----------- ---------- ----------- -----------
3 小刚 95 1
4 小李 90 2
1 小明 80 3
2 小华 80 3
*/