szhswl
          宋針還的個人空間

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

          2、刪除表中多余的重復記錄,重復記錄是根據單個字段(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、查找表中多余的重復記錄(多個字段)
          select * from vitae a
          where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

          4、刪除表中多余的重復記錄(多個字段),只留有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、查找表中多余的重復記錄(多個字段),不包含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重復記錄的查詢還可以用下面SQL:
          select * from people p1
          where p1.rowid not in (select max(rowid) from people p2 where p2.peopleId = p1.peopleId)

          ---------------------------------------------------------------------------------------------------------------------------------
          說人之短,乃護己之短??浼褐L,乃忌人之長。皆由存心不厚,識量太狹耳。能去此弊,可以進德,可以遠怨。
          http://www.aygfsteel.com/szhswl
          ------------------------------------------------------------------------------------------------------ ----------------- ---------
          posted on 2007-12-03 15:48 宋針還 閱讀(721) 評論(0)  編輯  收藏 所屬分類: SQL
          主站蜘蛛池模板: 西城区| 江油市| 扶余县| 南城县| 涿州市| 武平县| 望城县| 商丘市| 哈尔滨市| 惠来县| 天台县| 罗源县| 凤山市| 中山市| 新建县| 呼图壁县| 灌阳县| 昌江| 洪湖市| 志丹县| 常山县| 昭觉县| 城固县| 邢台县| 长泰县| 通山县| 巫山县| 财经| 玉田县| 长春市| 孙吴县| 桂东县| 吉首市| 铁力市| 交城县| SHOW| 堆龙德庆县| 桐庐县| 平阴县| 分宜县| 成安县|