ROW_NUMBER() OVER函数学习

我的数据库表有ID，NAME ,ADCODE,CITYNAME等列名，其中ID是地址编号，NAME是村镇等名，CITYNAME是城市名，ADCODE是城市编号

一、下面我的代码是查询ID(地址编号)重复数据，下面我写了两种查询方式：

用分组函数的方法查询CITYNAME(城市名)为广州市，ID(地址编号)重复的数据
SELECT A.*
FROM ADDRESS A
WHERE CITYNAME LIKE ‘%广州市%’
AND ROWID IN (SELECT MAX(ROWID)
FROM ADDRESS B
WHERE B.CITYNAME = ‘广州市’
GROUP BY B.ID
HAVING COUNT(B.ID) > 1)

2.ROW_NUMBER() OVER(PARTITION BY 列 ORDER BY 列 DESC)函数查询CITYNAME(城市名)为广州市，ID(地址编号)重复数据，关于ROW_NUMBER() OVER()函数见博客地址:https://blog.csdn.net/ming614/article/details/81624757
SELECT B.*
FROM (SELECT A.*,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ROWID ASC) AS ROW_FLG
FROM address A) B
WHERE B.ROW_FLG > 1 AND CITYNAME = ‘广州市’

二、去重复查询，下面我写了三种查询方式

用分组函数的方法查询CITYNAME(城市名)为广州市，去重复查询ID(地址编号)的数据
SELECT A.*
FROM ADDRESS A
WHERE CITYNAME =’%广州市%’
AND ROWID IN (SELECT MAX(ROWID)
FROM ADDRESS B
WHERE B.CITYNAME = ‘广州市’
GROUP BY B.ID)

用DISTINCT函数的方法去重复查询CITYNAME(城市名)为广州市，ID(地址编号)的数据

SELECT DISTINCT ID,CITYNAME FORM ADDRESS WHERE CITYNAME = ‘广州市’

用ROW_NUMBER() OVER(PARTITION BY 列 ORDER BY 列 DESC)函数的方法查询CITYNAME(城市名)为广州市，去重复查询ID(地址编号)的数据

SELECT B.*
FROM (SELECT A.*,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ROWID ASC) AS ROW_FLG
FROM address A) B
WHERE B.ROW_FLG > 1 AND CITYNAME = ‘广州市’

三、删除重发数据，下面我写了两种方法

用分组函数的方法删除CITYNAME(城市名)为广州市，去重复ID(地址编号)的数据
DELETE FROM ADDRESS
WHERE CITYNAME = ‘广州市’
AND ROWID NOT IN
(SELECT MAX(ROWID) FROM ADDRESS WHERE CITYNAME = ‘广州市’ GROUP BY ID)

用ROW_NUMBER() OVER(PARTITION BY 列 ORDER BY 列 DESC)函数的方法删除CITYNAME(城市名)为广州市，ID(地址编号)重复的数据

DELETE FROM ADDRESS
WHERE ROWID IN (SELECT ROWID
FROM (SELECT A.*,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ROWID ASC) AS ROW_FLG
FROM ADDRESS A) B
WHERE B.ROW_FLG > 1
AND CITYNAME = ‘广州市’)