查询重复数据和删除重复的数据的方式

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

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

1. 用分组函数的方法查询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 = '广州市'

    

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

1. 用分组函数的方法查询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)

    

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

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

    

3. 用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 = '广州市'

    

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

1. 用分组函数的方法删除CITYNAME(城市名)为广州市，去重复ID(地址编号)的数据

   DELETE FROM ADDRESS
    WHERE CITYNAME = '广州市'
      AND ROWID NOT IN
          (SELECT MAX(ROWID) FROM ADDRESS WHERE CITYNAME = '广州市' GROUP BY ID)
   
   

    

2. 用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 = '广州市')