MySql:在并中保持序的所有结果

Question

我有以下几点：

with
result1 as ( select column1 from table1 order by ... )
result2 as ( select column1 from table2 order by ... )
select * from result1 union all select * from result2;

其目的是列出result1及其保留的顺序，然后列出保留其顺序的result2。但是，union all似乎没有保留其操作数表的顺序。我怎样才能达到预期的效果？我尝试将列添加到有序的result1和result2中，然后在这个添加的列上排序联合结果，但是对于一些看起来非常典型的内容，这看起来非常令人乏味。有像union all ordered这样的东西吗

Answer 1

好的。下面是使用“窗口函数”进行排序的方法。如果您使用的是MySQL 8+

with
result1 as ( 
select column1, ROW_NUMBER() OVER (ORDER BY ...) AS rownum1, 0 AS rownum2, 
from table1 order by ...
),
result2 as ( 
select column1, 0 AS rownum1, ROW_NUMBER() OVER (ORDER BY ...) AS rownum2
from table2 order by ...
),
result as (
select * from result1 
union all
select * from result2 
)
     
select * from result order by rownum2, rownum1