--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