公司让写一个报表，有如下sql:
SELECT id,time FROM (
SELECT aid AS id,atime AS time FROM a
UNION ALL
SELECT bid AS id,btime AS time FROM b
) AS a
然后查处如下记录:
[
{
"id":2,
"time":10
},
{
"id":1,
"time":20
},
{
"id":1,
"time":30
},
{
"id":1,
"time":40
},
]
然后id重复的记录希望取time值最大的
这是改变后的sql:
SELECT id,time FROM (
SELECT aid AS id,atime AS time FROM a
UNION ALL
SELECT bid AS id,btime AS time FROM b
) AS a GROUP BY a.id ORDER BY time DESC
可是查到的并不是我想要的数据
[
{
"id":2,
"time":10
},
{
"id":1,
"time":20
}
]
然后我们发现后面的order by 其实并没有起作用
接下来我们把sql修改如下：
SELECT id,time FROM (
SELECT id,time FROM (
SELECT aid as id,atime as time FROM a
UNION ALL
SELECT bid as id,btime as time FROM b
) AS b ORDER BY time DESC
) AS a GROUP BY a.id
发现结果还是一样的，order by 还是没有起作用
敲黑板
最后我了解到，如果order by 不带limit，会被优化器干掉，导致语句就是：
SELECT id,time FROM (
SELECT id,time FROM (
SELECT aid as id,atime as time FROM a
UNION ALL
SELECT bid as id,btime as time FROM b
) AS b
) AS a GROUP BY a.id
解决方案：
SELECT id,time FROM (
SELECT id,time FROM (
SELECT aid as id,atime as time FROM a
UNION ALL
SELECT bid as id,btime as time FROM b
) AS b ORDER BY time DESC LIMIT 999
) AS a GROUP BY a.id
至此，完美解决
至于为什么出现这种情况，我只找到这一段话
If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.
One factor that affects the execution plan is LIMIT, so an ORDER BY query with and without LIMIT may return rows in different orders. Consider this query, which is sorted by the category column but nondeterministic with respect to the id and rating columns:
自行翻译
有limit和无limit ,order by查询可能返回不同顺序的行