如何在不排序的情况下执行 UNION？(SQL)

How to execute UNION without sorting? (SQL)

提问by hightow

UNIONjoins two results and remove duplicates, while UNION ALLdoes not remove duplicates.
UNIONalso sort the final output.

UNION连接两个结果并删除重复项，UNION ALL而不删除重复项。
UNION还对最终输出进行排序。

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

我想要的是UNION ALL没有重复和没有排序。那可能吗？

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).

这样做的原因是我希望第一个查询的结果在最终结果之上，第二个查询在底部（并且每个查询都像它们单独运行一样排序）。

回答by Martin Smith

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

我注意到这个问题得到了很多观点，所以我会先解决一个你没有问过的问题！

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

关于标题。要实现“Sql Union All with“distinct””，只需将其替换UNION ALL为UNION. 这具有删除重复项的效果。

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  

回答by Damien_The_Unbeliever

"UNION also sort the final output" - only as an implementation artifact. It is by no meansguaranteed to perform the sort, and if you need a particular sort order, you should specify it with an ORDER BYclause. Otherwise, the output order is whatever is most convenient for the server to provide.

“UNION 也对最终输出进行排序” - 仅作为实现工件。这是没有办法保证执行排序，如果你需要一个特定的排列顺序，你应该以指定它ORDER BY的条款。否则，输出顺序是服务器提供的最方便的顺序。

As such, your request for a function that performs a UNION ALLbut that removes duplicates is easy - it's called UNION.

因此，您对执行 aUNION ALL但删除重复项的函数的请求很容易 - 它被称为UNION。

---

---

From your clarification, you also appear to believe that a UNION ALLwill return all of the results from the first query before the results of the subsequent queries. This is alsonot guaranteed. Again, the only way to achieve a particular order is to specify it using an ORDER BYclause.

根据您的说明，您似乎还相信 aUNION ALL将在后续查询的结果之前返回第一个查询的所有结果。这也不能保证。同样，实现特定顺序的唯一方法是使用ORDER BY子句指定它。

回答by onedaywhen

SELECT *, 1 AS sort_order
  FROM table1
 EXCEPT 
SELECT *, 1 AS sort_order
  FROM table2
UNION
SELECT *, 1 AS sort_order
  FROM table1
 INTERSECT 
SELECT *, 1 AS sort_order
  FROM table2
UNION
SELECT *, 2 AS sort_order
  FROM table2
 EXCEPT 
SELECT *, 2 AS sort_order
  FROM table1
ORDER BY sort_order;

But the real answer is: other than the ORDER BYclause, the sort order will by arbitrary and not guaranteed.

但真正的答案是：除了ORDER BY子句之外，排序顺序是任意的，不能保证。

回答by onedaywhen

Consider these tables (Standard SQL code, runs on SQL Server 2008):

考虑这些表（标准 SQL 代码，在 SQL Server 2008 上运行）：

WITH A 
     AS 
     (
      SELECT * 
        FROM (
              VALUES (1), 
                     (2), 
                     (3), 
                     (4), 
                     (5), 
                     (6) 
             ) AS T (col)
     ),
     B 
     AS 
     (
      SELECT * 
        FROM (
              VALUES (9), 
                     (8), 
                     (7), 
                     (6), 
                     (5), 
                     (4) 
             ) AS T (col)
     ), ...

The desired effect is this to sort table Aby colascending, sort table Bby coldescending then unioning the two, removing duplicates, retaining order before the union and leaving table Aresults on the "top" with table Bon the "bottom" e.g. (pesudo code)

所需的效果是A按col升序对表B进行排序，col按降序对表进行排序，然后将两者A合并，删除重复项，保留合并前的顺序，并将表结果保留在“顶部”，将表B放在“底部”，例如（伪代码）

(
 SELECT *
   FROM A
  ORDER 
     BY col
)
UNION
(
 SELECT *
   FROM B
  ORDER 
     BY col DESC
);

Of course, this won't work in SQL because there can only be one ORDER BYclause and it can only be applied to the top level table expression (or whatever the output of a SELECTquery is known as; I call it the "resultset").

当然，这在 SQL 中是行不通的，因为只能有一个ORDER BY子句，而且它只能应用于顶级表表达式（或SELECT查询的任何输出；我称之为“结果集”）。

The first thing to address is the intersection between the two tables, in this case the values 4, 5and 6. How the intersection should be sorted needs to be specified in SQL code, therefore it is desirable that the designer specifies this too! (i.e. the person asking the question, in this case).

首先要解决的是两个表之间的交集，在本例中是值4,5和6。交集应该如何排序需要在SQL代码中指定，所以最好设计者也指定！（即在这种情况下提出问题的人）。

The implication in this case would seem to be that the intersection ("duplicates") should be sorted within the results for table A. Therefore, the sorted resultset should look like this:

这种情况下的含义似乎是交集（“重复”）应该在表 A 的结果中排序。因此，排序后的结果集应如下所示：

      VALUES (1), -- A including intersection, ascending
             (2), -- A including intersection, ascending
             (3), -- A including intersection, ascending
             (4), -- A including intersection, ascending
             (5), -- A including intersection, ascending
             (6), -- A including intersection, ascending
             (9), -- B only, descending 
             (8), -- B only, descending  
             (7), -- B only, descending 

Note in SQL "top" and "bottom" has no inferent meaning and a table (other than a resultset) has no inherent ordering. Also (to cut a long story short) consider that UNIONremoves duplicate rows by implication and must be applied before ORDER BY. The conclusion has to be that each table's sort order must be explicitly defined by exposing a sort order column(s) beforebeing unioned. For this we can use the ROW_NUMBER()windowed function e.g.

注意 SQL 中的“顶部”和“底部”没有推论意义，并且表（结果集除外）没有固有的顺序。另外（长话短说）考虑UNION通过隐含删除重复行并且必须在ORDER BY. 结论必须是每个表的排序顺序必须通过在联合之前公开排序顺序列来明确定义。为此，我们可以使用ROW_NUMBER()窗口函数，例如

     ...
     A_ranked
     AS
     (
      SELECT col, 
             ROW_NUMBER() OVER (ORDER BY col) AS sort_order_1
        FROM A                      -- include the intersection
     ),
     B_ranked
     AS
     (
      SELECT *, 
             ROW_NUMBER() OVER (ORDER BY col DESC) AS sort_order_1
        FROM B
       WHERE NOT EXISTS (           -- exclude the intersection
                         SELECT * 
                           FROM A
                          WHERE A.col = B.col 
                        )
     )
SELECT *, 1 AS sort_order_0 
  FROM A_ranked
UNION
SELECT *, 2 AS sort_order_0 
  FROM B_ranked
ORDER BY sort_order_0, sort_order_1;

回答by Mikael Eriksson

select T.Col1, T.Col2, T.Sort
from 
    (
      select T.Col1,
             T.Col2,
             T.Sort,
             rank() over(partition by T.Col1, T.Col2 order by T.Sort) as rn
      from
          (
            select Col1, Col2, 1 as Sort
            from Table1
            union all
            select Col1, Col2, 2
            from Table2
          ) as T
    ) as T
where T.rn = 1    
order by T.Sort

回答by Divya Agrawal

Try this:

尝试这个：

  SELECT DISTINCT * FROM (

      SELECT  column1, column2 FROM Table1
      UNION ALL
      SELECT  column1, column2 FROM Table2
      UNION ALL
      SELECT  column1, column2 FROM Table3

  ) X ORDER BY Column1

回答by Rowland Shaw

The sort is used to eliminate the duplicates, and is implicit for DISTINCTand UNIONqueries (but not UNION ALL) - you could still specify the columns you'd prefer to order by if you need them sorted by specific columns.

排序用于消除重复项，并且对于DISTINCT和UNION查询是隐式的（但不是UNION ALL） - 如果您需要按特定列对它们进行排序，您仍然可以指定您希望排序的列。

For example, if you wanted to sort by the result sets, you could introduce an additional column, and sort by that first:

例如，如果您想按结果集排序，您可以引入一个额外的列，并首先按该列排序：

SELECT foo, bar, 1 as ResultSet
FROM Foo
WHERE bar = 1
UNION
SELECT foo, bar, 2 as ResultSet
FROM Foo
WHERE bar = 3
UNION
SELECT foo, bar, 3 as ResultSet
FROM Foo
WHERE bar = 2
ORDER BY ResultSet

回答by dursun

I assume your tables are table1 and table2 respectively, and your solution is;

我假设你的表分别是 table1 和 table2，你的解决方案是；

(select * from table1 MINUS select * from table2)
UNION ALL
(select * from table2 MINUS select * from table1)

回答by Gank

1,1: select 1 from dual union all select 1 from dual 1: select 1 from dual union select 1 from dual

1,1: select 1 from dual union all select 1 from dual 1: select 1 from dual union select 1 from dual

回答by Rohit Jain

You can do something like this.

你可以做这样的事情。

Select distinct name from  (SELECT r.name FROM outsider_role_mapping orm1 
    union all
SELECT r.name FROM user_role_mapping orm2
) tmp;