场景: 按照创建时间倒序排列获取手机号,并根据userId进行分组
错误示例:
select *
from (
select
ec.user_id userId,ec.PHONE phone
from contact ec
where ec.`status`=1
and ec.user_id in ("1","2")
and ec.phone is not null
and ec.phone != ''
order by ec.create_time desc)as a
group by a.user_id
错误原因:
mysql的执行顺序是:select -> from -> where -> group by -> having -> order by
以上错误示例相当于按照Mysql的默认排序进行分组,获取到的数据大概率是异常的。
MySQL的默认排序规则:
1.如果查询条件无索引列,默认按主键正序排序。
2.查询条件中有索引列,默认顺序为:主键 > 唯一索引 > 普通索引,如果在SQL中查询条件同时存在有多个,那么按照索引最先创建的顺序进行正序排序。
例:SELECT * FROM a WHERE a.id = ‘a’ and a.user_id = ‘a’;
如果id和user_id都是索引,id先创建,则按照id进行正序排序。
正确示例:
select
e.user_id userId,e.PHONE phone
from contact e
join (
select
max(create_time) create_time
FROM contact ec
WHERE ec.`status` = 1
AND ec.phone IS NOT NULL
AND ec.phone != ''
and ec.user_id in ('1','2')
group by ec.user_id)as a on e.create_time = a.create_time
and e.`status` = 1
AND e.user_id in ('1','2')
AND e.phone IS NOT NULL
AND e.phone != ''
group by e.user_id
先group by 获取到最大的创建时间,然后join表查询当前最大创建时间下的数据
注意:
如果最大的创建时间一致的有两条记录,以上示例没有进行处理,使用group by默认排序