关于ROW_NUMBER函数的使用(The use of ROW_NUMBER function )
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
