mysql - MySql：在 UNION ALL 结果中保留顺序

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吗？

标签： mysqlsql-order-byunion

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