mysql - 为什么内部 ORDER BY 不起作用？

Question

这是我的查询结构:

(select @rnk := @rnk + 2 `rank` ,* from table1, (select @rnk := -1) x order by col1)
union all
(select @rnk2 := @rnk2 + 2 `rank`,* from table2, (select @rnk2 := 0) x)
order by rank limit 10

根据结果，我认为 order by col1 不起作用。为什么？

我想要的是:首先，首先 select 按 order by col1 排序，然后最终结果按 order by rant 交替>。怎么做到的？

---

编辑:想象一下这个结果:

select @rnk := @rnk + 2 `rank` ,* from table1, (select @rnk := -1) x order by col1

// output: blut, green, red


select @rnk2 := @rnk2 + 2 `rank`,* from table2, (select @rnk2 := 0) x

// output: five, three, one, six, seven, two

现在我想要这个顺序用于输出:

blue, five, green, three, red, one, six, seven, two

但是上面的查询不是这样的。为什么？

Answer 1

将order by col1放在另一层子查询中:

(SELECT @rnk := @rnk + 2 as rank, t.*
 FROM (SELECT * FROM table1 ORDER BY col1) AS t
 CROSS JOIN (SELECT @rnk := -1) AS x)
UNION ALL
(SELECT @rnk2 := @rnk2 + 2 AS rank, *
 FROM table2
 CROSS JOIN (SELECT @rnk2 := 0) AS x)
ORDER BY rank

有时在分配 rank 列的同一个查询中进行排序是有效的，但不能保证，所以最好在子查询中进行。