按同一字段排序，left join 之后顺序与原表直接排序不一致，left join与distinct处理数据重复(统计场景) distinct排序时会对id优化，导致顺序不一致

问题由来：一次性插入多条数据，插入时顺序正常，id正常，但id大的更新时间小！ 

SELECT
	* 
FROM
	t_product_events 
WHERE
	product_id = 1344663677526016 
ORDER BY
	update_time DESC

SELECT DISTINCT
	pe.id,
	pe.product_id,
	pe.identifier,
	pe.NAME,
	pe.description,
	pe.type,
	pe.original_required,
	pe.update_required,
	pe.custom,
	pe.copyright,
	pe.prerelease,
	pe.method,
	pe.LEVEL,
	pe.create_time,
	pe.update_time,
	( SELECT count( * ) FROM t_product_events_params WHERE ref_id = pe.id ) AS outNum 
FROM
	t_product_events pe
	LEFT JOIN t_product_events_params pep ON pe.id = pep.ref_id 
WHERE
	pe.product_id = 1344663677526016 
ORDER BY
	pe.update_time DESC

批量插入的数据为红色方框中的

 左表数据：

连接表数据：

左连接不带distinct查询语句：

SELECT 
	pe.id,
	pe.product_id,
	pe.identifier,
	pe.NAME,
	pe.description,
	pe.type,
	pe.original_required,
	pe.update_required,
	pe.custom,
	pe.copyright,
	pe.prerelease,
	pe.method,
	pe.LEVEL,
	pe.create_time,
	pe.update_time,
	( SELECT count( * ) FROM t_product_events_params WHERE ref_id = pe.id ) AS outputData 
FROM
	t_product_events pe
	LEFT JOIN t_product_events_params pep ON pe.id = pep.ref_id 
WHERE
	pe.product_id = 1344663677526016 
ORDER BY
	pe.update_time DESC

不带distinct：

 带上distinct：

distinct对id进行了重新排序导致！！！

解决方案修改sql:

1. distinct换成group by

2. order by pe.create_time desc 换成order by pe.create_time desc,pe.id

修改方案后的SQL语句：

SELECT
DISTINCT
	pe.id,
	pe.product_id,
	pe.identifier,
	pe.NAME,
	pe.description,
	pe.type,
	pe.original_required,
	pe.update_required,
	pe.custom,
	pe.copyright,
	pe.prerelease,
	pe.method,
	pe.LEVEL,
	pe.create_time,
	pe.update_time,
	( SELECT count( * ) FROM t_product_events_params WHERE ref_id = pe.id ) AS outputData 
FROM
	t_product_events pe
	LEFT JOIN t_product_events_params pep ON pe.id = pep.ref_id 
WHERE
	pe.product_id = 1344663677526016 
ORDER BY
	pe.update_time DESC,pe.id
	

结果

 

 

总结

distinct id，会对id进行重新排序，如果分页结果显示顺序会有问题。 order by避免和distinct一起使用，尽量和group by去重。