一个偶然的机会，发现一条SQL语句在不同的MySQL实例上执行得到了不同的结果。
问题描述
创建门店维表表dim_store，来模拟下业务场景，结构和数据如下：
SELECT store_id,
seller_id,
GROUP_CONCAT(org_name, '-') AS org_path
FROM (SELECT store_id, seller_id, org_name
FROM canytest.`dim_store`
WHERE seller_id IN (211302) AND store_id = 144994
ORDER BY org_level DESC) t
GROUP BY store_id,
seller_id通过结果可以看到，子查询先将dim_store里的所有记录按等级(org_level)逆序，进而查询出门店最新等级。通过几次查询得到的结果是不相同的；
问题根因
A “table” (and subquery in the FROM clause too) is - according
to the SQL standard - an unordered set of rows. Rows in a table
(or in a subquery in the FROM clause) do not come in any specific
order. That’s why the optimizer can ignore the ORDER BY clause
that you have specified. In fact, SQL standard does not even allow the ORDER BY clause to appear in this subquery (we allow it, because ORDER BY … LIMIT … changes the result, the set of rows, not only their order).
You need to treat the subquery in the FROM clause, as a set of
rows in some unspecified and undefined order, and put the
ORDER BY on the top-level SELECT.问题的原因清晰了，原来SQL标准中，table的定义是一个未排序的数据集合，而一个SQL子查询是一个临时的table，根据这个定义，子查询中的order by会被忽略。同时，官方回复也给出了解决方案：将子查询的order by移动到最外层的select语句中。
总结
- 在SQL标准中，子查询中的order by是不生效的
- MySQL 5.7由于在这个点上遵循了SQL标准导致问题暴露，而在MySQL 5.6/8.0中这种写法依然是生效的
参考文档
https://stackoverflow.com/questions/26372511/mysql-mariadb-order-by-inside-subquery
https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/