重复数据查询、删除 group by having使用

--查询重复记录，单个字段或多个字段约束
 select 重复字段名
   from 表名
  group by 重复字段名
 having count(重复字段名) > 1

例：
 select t.mark_id
   from LOSS_F_MARK_STATUS t
  where t.sbtype = '1'
  group by t.mark_id
 having count(t.mark_id) >= 2
--或者  having count(t.mark_id) > 1

例：
 select t.mark_id, t.obj_id
   from LOSS_F_MARK_STATUS t
  where t.sbtype = '1'
  group by t.mark_id, t.obj_id
 having count(t.mark_id) > 1

--查询表中重复记录

例：
select *  
 from LOSS_F_MARK_STATUS t1
 where t1.mark_id in (select t.mark_id
                        from LOSS_F_MARK_STATUS t
                       where t.sbtype = '1'
                       group by t.mark_id
                      having count(t.mark_id) >= 2)

--删除重复记录，只保留一条

例：
delete
  from LOSS_F_MARK_STATUS t1
 where t1.mark_id in (select t.mark_id
                        from LOSS_F_MARK_STATUS t
                       where t.sbtype = '1'
                       group by t.mark_id
                      having count(t.mark_id) >= 2)
   --and t1.sbtype = '1'
   and rowid not in
       (select max(rowid) from LOSS_F_MARK_STATUS t2 group by t2.mark_id)
--保留rowid 最大的记录

--删除重复记录，保留rowid最小的记录
delete
  from LOSS_F_MARK_STATUS t1
 where t1.mark_id in (select t.mark_id
                        from LOSS_F_MARK_STATUS t
                       where t.sbtype = '1'
                       group by t.mark_id
                      having count(t.mark_id) >= 2)
   --and t1.sbtype = '1'
   and rowid not in
       (select min(rowid) from LOSS_F_MARK_STATUS t2 group by t2.mark_id having count(*)>1) 
--不好用，删除3条以上记录好用。删除重复两条记录时，把只有一条的记录删掉了