一、简单常用的方式
1、简单的去重“distinct”
select distinct t.id from user t
2、group by 分组去重
select id,name from user group by id,name
二、组合去重方式
有很多时候 上面两个不能满足的去重场景,比如去重后显示多列,那么就需要用到组合去重了
1、group by + ROWID in查询 去重
select t.id,t.name,t.sex from user t where t.ROWID in
(select max(ROWID) from user group by id)
2、group by + ROWID 关联查询去重
select t.id,t.name,t.sex from user t,
(select max(ROWID) ROWID2 from user group by id) t2
where t.ROWID = t2.ROWID2
3、max + group by 去重(效率最高)
select t.id,max(t.name),max(t.sex) from user t where group by t.id