Sqlserver中ROW_NUMBER分组去重

 

--PARTITION BY分组

DECLARE @table TABLE (
 code VARCHAR(100)
 ,name NVARCHAR(100)
 
)

INSERT INTO @table
(
 code
    ,name
)
VALUES('张三','aa'),('张三','aa'),('张三','aa'),('李四','bb'),('李四','bb'),('李四','bb'),('王五','cc'),('王五','cc'),('王五','cc'),('张三','cc')

SELECT
 ROW_NUMBER() OVER(PARTITION BY code ORDER BY (SELECT 1)) num
 ,code
 ,name
 FROM @table

 SELECT
 ROW_NUMBER() OVER(PARTITION BY code,name ORDER BY (SELECT 1)) num
 ,code
 ,name
 FROM @table