如何不排序就执行UNION？ （SQL） [英] How to execute UNION without sorting? (SQL)

问题描述

UNION 合并两个结果并删除重复项，而 UNION ALL 不会删除重复项。

UNION 还会对最终输出进行排序。

UNION joins two results and remove duplicates, while UNION ALL does not remove duplicates.
UNION also sort the final output.

我想要的是 UNION ALL 没有重复，也没有排序。

What I want is the UNION ALL without duplicates and without the sort. Is that possible?

原因是我希望第一个查询的结果位于最终结果的顶部，第二个查询的结果位于底部的（并对其进行排序，就好像它们分别在其中运行一样。）

The reason for this is that I want the result of the first query to be on top of the final result, and the second query at the bottom (and each sorted as if they where run individually).

推荐答案

我注意到这个问题获得了很多见解，所以我将首先解决您没有提出的问题！

I notice this question gets quite a lot of views so I'll first address a question you didn't ask!

关于标题。要实现 Sql Union All with distinct，只需将 UNION ALL 替换为 UNION 。

Regarding the title. To achieve a "Sql Union All with "distinct"" then simply replace UNION ALL with UNION. This has the effect of removing duplicates.

对于您的特定问题，给出的澄清是第一个查询应具有优先级，因此应从底部删除重复项可以使用

For your specific question, given the clarification "The first query should have "priority", so duplicates should be removed from bottom" you can use

SELECT col1,
       col2,
       MIN(grp) AS source_group
FROM   (SELECT 1 AS grp,
               col1,
               col2
        FROM   t1
        UNION ALL
        SELECT 2 AS grp,
               col1,
               col2
        FROM   t2) AS t
GROUP  BY col1,
          col2
ORDER  BY MIN(grp),
          col1  

这篇关于如何不排序就执行UNION？ （SQL）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！