关于ROW_NUMBER函数的使用(The use of ROW_NUMBER function )

时间:2024-07-14 17:06:08

1、用于删除重复记录(The use of to delete the common record)

例子:(Example)

#1初始化数据(Initialize the data)

CREATE TABLE #tmp1 ( id int, name nvarchar(20), age int );

INSERT INTO #tmp1 VALUES(1,'CangoWu',25)

INSERT INTO #tmp1 VALUES(2,'CangoWu',25)

INSERT INTO #tmp1 VALUES(3,'CangoWu',25)

INSERT INTO #tmp1 VALUES(4,'Ajay',29)

INSERT INTO #tmp1 VALUES(5,'Ajay',29)

INSERT INTO #tmp1 VALUES(6,'Ajay',29)

#2操作SQL(T-SQL)

--操作前(Before the operation)

SELECT * FROM #tmp1

id name age
1 CangoWu 25
2 CangoWu 25
3 CangoWu 25
4 Ajay 29
5 Ajay 29
6 Ajay 29

--操作后(After the operation)

SELECT name,age FROM
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY name ORDER BY id) row FROM #tmp1
)tmp
WHERE row = 1

name age
Ajay 29
CangoWu 25

2、记录相同字段不重复显示(The record fields repeat same display)

例子:(Example)

#1初始化数据(Initialize the data)

CREATE TABLE #tmp2 ( id int, name nvarchar(20), age int, month int, salary int );

INSERT INTO #tmp2 VALUES(1,'CangoWu',25,1,2500)

INSERT INTO #tmp2 VALUES(2,'CangoWu',25,2,2500)

INSERT INTO #tmp2 VALUES(3,'CangoWu',25,3,2500)

INSERT INTO #tmp2 VALUES(4,'CangoWu',25,4,2500)

INSERT INTO #tmp2 VALUES(5,'CangoWu',25,5,2500)

INSERT INTO #tmp2 VALUES(6,'CangoWu',25,6,2500)

INSERT INTO #tmp2 VALUES(11,'Preston',35,1,8000)

INSERT INTO #tmp2 VALUES(12,'Preston',35,2,8000)

INSERT INTO #tmp2 VALUES(13,'Preston',35,3,8000)

INSERT INTO #tmp2 VALUES(14,'Preston',35,4,8000)

INSERT INTO #tmp2 VALUES(15,'Preston',35,5,8000)

INSERT INTO #tmp2 VALUES(16,'Preston',35,6,8000)

INSERT INTO #tmp2 VALUES(21,'Ajay',29,1,5000)

INSERT INTO #tmp2 VALUES(22,'Ajay',29,2,5000)

INSERT INTO #tmp2 VALUES(23,'Ajay',29,3,5000)

INSERT INTO #tmp2 VALUES(24,'Ajay',29,4,5000)

INSERT INTO #tmp2 VALUES(25,'Ajay',29,5,5000)

INSERT INTO #tmp2 VALUES(26,'Ajay',29,6,5000)

#2操作SQL(T-SQL)

SELECT
name = (CASE WHEN row = 1 THEN name ELSE '' END)
,age = (CASE WHEN row = 1 THEN age ELSE NULL END)
,month
,salary
FROM
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY name ORDER BY id) row FROM #tmp2
)tmp

name age month salary
Ajay 29 1 5000
     2 5000
     3 5000
     4 5000
     5 5000
     6 5000
CangoWu 25 1 2500
     2 2500
     3 2500
     4 2500
     5 2500
     6 2500
Preston 35 1 8000
     2 8000
     3 8000
     4 8000
     5 8000
     6 8000