szhswl
          宋針還的個人空間

          1、查找表中多余的重復(fù)記錄,重復(fù)記錄是根據(jù)單個字段(peopleId)來判斷
          select * from people
          where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

          2、刪除表中多余的重復(fù)記錄,重復(fù)記錄是根據(jù)單個字段(peopleId)來判斷,只留有rowid最小的記錄
          delete from people
          where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
          and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

          3、查找表中多余的重復(fù)記錄(多個字段)
          select * from vitae a
          where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

          4、刪除表中多余的重復(fù)記錄(多個字段),只留有rowid最小的記錄
          delete from vitae a
          where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
          and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

          5、查找表中多余的重復(fù)記錄(多個字段),不包含rowid最小的記錄
          select * from vitae a
          where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
          and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)


          利用rowid重復(fù)記錄的查詢還可以用下面SQL:
          select * from people p1
          where p1.rowid not in (select max(rowid) from people p2 where p2.peopleId = p1.peopleId)

          ---------------------------------------------------------------------------------------------------------------------------------
          說人之短,乃護(hù)己之短。夸己之長,乃忌人之長。皆由存心不厚,識量太狹耳。能去此弊,可以進(jìn)德,可以遠(yuǎn)怨。
          http://www.aygfsteel.com/szhswl
          ------------------------------------------------------------------------------------------------------ ----------------- ---------
          posted on 2007-12-03 15:48 宋針還 閱讀(727) 評論(0)  編輯  收藏 所屬分類: SQL
          主站蜘蛛池模板: 鸡东县| 荔浦县| 鄂伦春自治旗| 宁陵县| 彰武县| 合水县| 理塘县| 梨树县| 石首市| 台北县| 于都县| 剑阁县| 克拉玛依市| 花垣县| 仁怀市| 天全县| 从化市| 安龙县| 文山县| 海口市| 大丰市| 郯城县| 新丰县| 清新县| 杭州市| 高陵县| 宝坻区| 尼木县| 明溪县| 尚义县| 香格里拉县| 洱源县| 盘锦市| 桐城市| 色达县| 比如县| 铜川市| 湟中县| 鄂尔多斯市| 浦县| 屯昌县|