我的java天地

          oracle刪除重復記錄

          1.Oracle刪除重復記錄.
          刪除表中多余的重復記錄,重復記錄是根據單個字段(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)
          刪除表中多余的重復記錄(多個字段),只留有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)
          ================================
          此方法可以適用于sql ,oracle
          declare @max integer,@id integer
          declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >; 1
          open cur_rows
          fetch cur_rows into @id,@max
          while @@fetch_status=0
          begin
          select @max = @max -1
          set rowcount @max
          delete from 表名 where 主字段 = @id
          /*
          DECLARE @count INT
          SELECT @count = COUNT(*) FROM [table1] WHERE [column1] = 1
          DELETE TOP (@count-1) FROM [table1] WHERE [column1] = 1? 這個top后面一定要有括號
          */
          fetch cur_rows into @id,@max
          end
          close cur_rows
          set rowcount 0
          =======================================
          select distinct * into #Tmp from tableName
          drop table tableName
          select * into tableName from #Tmp
          drop table #Tmp
          select identity(int,1,1) as autoID, * into #Tmp from tableName
          select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
          select * from #Tmp where autoID in(select autoID from #tmp2)
          =======================================
          select identity(int,1,1) as id ,name,state into #tempTable from a
          delete from a
          delete from #tempTable
          where id not in
          (
          select min(id) from #tempTable group by name
          )
          insert into a( name,state)
          select name,state from #tempTable
          drop table #tempTable

          posted on 2010-03-10 14:43 tobyxiong 閱讀(544) 評論(0)  編輯  收藏 所屬分類: DATABASES

          <2010年3月>
          28123456
          78910111213
          14151617181920
          21222324252627
          28293031123
          45678910

          導航

          統計

          常用鏈接

          留言簿(3)

          隨筆分類(144)

          隨筆檔案(157)

          相冊

          最新隨筆

          搜索

          積分與排名

          最新評論

          閱讀排行榜

          評論排行榜

          主站蜘蛛池模板: 根河市| 左贡县| 兴和县| 茂名市| 安岳县| 壶关县| 含山县| 衡东县| 留坝县| 同仁县| 新竹市| 珲春市| 资源县| 湟源县| 屏东市| 久治县| 汾阳市| 通化市| 特克斯县| 天柱县| 内江市| 遂昌县| 丰宁| 梁山县| 股票| 嘉义县| 古蔺县| 中阳县| 铜梁县| 二连浩特市| 四子王旗| 崇左市| 柏乡县| 绥宁县| 色达县| 元阳县| 荔波县| 绿春县| 云南省| 通海县| 红原县|