使用“Union distinct"时保留来自子查询的记录顺序构造 [英] Preserving the order of records from subquery while using "Union distinct" construct

问题描述

我想确保在使用 Union distinct 时保留子查询结果的顺序.请注意，在进行联合时，需要使用union distinct"来过滤重复项.

I want to make sure that the order of the result from subquery are preserved while using Union distinct. Please note that "union distinct" is required to filter on duplicates while doing the union.

例如:

select columnA1, columnA2 from tableA order by [columnA3] asc
union distinct
select columnB1, columnB2 from tableB

当我运行这个时，我期望从子查询中排序的记录( select columnA1, columnA2 from tableA sort by [columnA3] asc) 首先出现(按照 columnA3 asc 的顺序返回)，然后是来自 tableB 的那些.

When I run this, I am expecting that the records ordered from subquery ( select columnA1, columnA2 from tableA sort by [columnA3] asc) comes in first (as returned by order by columnA3 asc) followed by those from tableB.

我假设我不能添加另一个虚拟列，因为这会使 union 不同而不起作用.所以，这行不通:

I am assuming that I cannot add another dummy column because that would make union distinct to not work. So, this won't work:

select column1, column2 from 
 ( select column1, column2, 1 as ORD from tableA order by [columnA3] asc
 union distinct
 select column1, column2, 2 as ORD from tableB 
 ) order by ORD

推荐答案

本质上，MySQL 在使用Union distinct"构造时不会保留来自子查询的记录顺序.经过一番研究，我发现如果我们放入限制子句或嵌套查询，它就可以工作.所以，下面是两种方法:

Essentially, MySQL isn’t preserving the order of records from sub-query while using "Union distinct" construct. After a bit of research, I found that it works if we put in a limit clause or have nested queries. So, below are the two approaches:

方法一:使用限制子句

         select columnA1, columnA2 from tableA order by [columnA3] asc Limit 100000000
         union distinct
         select columnB1, columnB2 from tableB

我已经使用很少的数据集测试了这种行为，它似乎始终如一地工作.此外，在 MySQL 的文档 ( http://dev.mysql.com/doc/refman/5.1/en/union.html ):对单个 SELECT 语句使用 ORDER BY 并不意味着行在最终结果中出现的顺序，因为 UNION 默认生成一组无序的行.因此，在此上下文中使用 ORDER BY 通常与 LIMIT 结合使用，以便它用于确定要为 SELECT 检索的所选行的子集，即使它不一定影响这些行在最终的 UNION 结果.如果 ORDER BY 在 SELECT 中出现而没有 LIMIT，则它会被优化掉，因为无论如何它都不会产生任何影响."

I have tested this behavior using few datasets and it seems to work consistently. Also, there is a reference to this behavior in MySQL‘s documentation ( http://dev.mysql.com/doc/refman/5.1/en/union.html ): "Use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows. Therefore, the use of ORDER BY in this context is typically in conjunction with LIMIT, so that it is used to determine the subset of the selected rows to retrieve for the SELECT, even though it does not necessarily affect the order of those rows in the final UNION result. If ORDER BY appears without LIMIT in a SELECT, it is optimized away because it will have no effect anyway."

请注意，选择 10000000000 的 LIMIT 没有特别的理由，只是要有足够高的数字以确保我们涵盖所有情况.

Please note that there is no particular reason in choosing LIMIT of 10000000000 other than having a sufficiently high number to make sure we cover all cases.

方法 2:如下所示的嵌套查询也有效.

Approach-2: A nested query like the one below also works.

        select column1, column2 from 
        ( select column1, column2 order by [columnA3] asc ) alias1
        union distinct
        ( select column1, column2 from tableB )

我找不到嵌套查询起作用的原因.网上有一些参考资料(比如 Phil McCarley 在 http://dev.mysql.com/doc/refman/5.0/en/union.html )但没有来自 MySQL 的官方文档.

I couldn’t find a reason for nested query to work. There have being some references online (like the one from Phil McCarley at http://dev.mysql.com/doc/refman/5.0/en/union.html ) but no official documentation from MySQL.

这篇关于使用“Union distinct"时保留来自子查询的记录顺序构造的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！