Mysql之 distinct & null | distinct 调优

一、distinct与null的关系见链接

https://www.cnblogs.com/jasonlam/p/6619013.html 

distinct show 的时候不会出现null或none

二、distinct调优

use test;
create table mytest(id int,name varchar(20))
insert into mytest values(1,'zs'),(2,'zs'),(3,'zs'),(4,'yan');

distinct适合单列使用:

select distinct name from users 

以下写法直接拿两列联合去重：

select distinct id,name from users

以下写法直接报错：

select id,distinct name from users

=> 如何高效率的去重单列，又保留其他字段？  

# id最大之前的都返回（这些是需要delete的）

select a.* from mytest a where exists(select 1 from mytest b where a.name = b.name and a.id < b.id )

# id最小之后的都返回

select a.* from mytest a where exists(select 1 from mytest b where a.name = b.name and a.id > b.id )

结论：

select a.* from mytest a where exists
(select 1 
 from 
   (select max(id) id,name from mytest group by name)b 
where a.name = b.name and a.id = b.id
)

## （不删，直接select去重）数据量越大，性能越强
## 任意相同，而不是全部相同