SQL开发中常用到的几种去重方案

SQL开发中常用到的几种去重方案

---

distinct去重

• stinct只能返回他的目标字段，而无法返回其他字段，

select distinct name from user

一般distinct用来查询不重复记录的条数。

group by去重

• 如果要查询不重复的记录，有时候可以用group by,但是无法获取到最新的数据

select * from oc_ele_chat a where source_id = 1 and to_id=54 group by source_id order by id desc

可以看出，通过order by desc也无法获取的最新的数据.

not exists 去重

• not exists 去重是根据添加的最新时间来排序

  select * from oc_ele_chat a where source_id = 1 and class = 3 and not exists (select * from oc_ele_chat b where a.`to_id` = b.`to_id` and created_time >a.created_time)

子查询方法

select * from oc_ele_chat A where A.class = 45 and ((A.source_id =3 and A.reply_type = 2) or A.source_id =2) and A.id in (select MAX(id) from oc_ele_chat B where (B.from_id=A.from_id and B.to_id=A.to_id) or (B.to_id=A.from_id and B.from_id=A.to_id))

in方式去重

select * from oc_ele_chat a where id in(select max(id) id from oc_ele_chat b where source_id = 2 and from_id = 67 group by to_id order by id desc)

第二种案例

select * from oc_ele_chat A where (A.source_id =2 or (A.source_id =3 and A.reply_type = 2)) and (A.from_id = 67 or A.to_id = 67) and A.id in (select MAX(id) from oc_ele_chat B where (B.from_id=A.from_id and B.to_id=A.to_id) or (B.to_id=A.from_id and B.from_id=A.to_id)) order by A.id desc

利用最大的id值进行判断去重

select * from oc_ele_chat  A where A.class = 45 and ((A.source_id =3 and A.reply_type = 2) or A.source_id =2) and not exists
       (select 1 from oc_ele_chat  B
       where ((B.from_id=A.from_id and B.to_id=A.to_id)
              or (B.to_id=A.to_id and B.from_id=A.to_id))
              and B.id>A.id)