如何在UNION中使用ORDER BY

I want to use ORDER BY on every UNION ALL queries, but I can't figure out the right syntax. This is what I want:

我想在每个UNION ALL查询中使用ORDER BY,但我无法弄清楚正确的语法。这就是我要的:

(
SELECT id, user_id, other_id, name 
FROM tablename 
WHERE user_id = 123 AND user_in IN (...) 
ORDER BY name
)
UNION ALL
(
SELECT id, user_id, other_id, name 
FROM tablename 
WHERE user_id = 456 AND user_id NOT IN (...) 
ORDER BY name
)

EDIT: Just to be clear: I need two ordered lists like this, not one:

编辑:要明确:我需要两个这样的有序列表,而不是一个:

1 2 3 1 2 3 4 5

1 2 3 1 2 3 4 5

Thank you very much!

非常感谢你!

4 个解决方案

#1

Something like this should work in MySQL:

像这样的东西应该在MySQL中起作用:

SELECT a.*
  FROM ( 
         SELECT ...  FROM ... ORDER BY ... 
       ) a
 UNION ALL 
SELECT b.*
  FROM ( 
         SELECT ...  FROM ... ORDER BY ... 
       ) b

Note however, absent an ORDER BY (or GROUP BY) clause on the outermost query, the order that the rows are returned is NOT guaranteed.

但请注意,如果最外层查询没有ORDER BY(或GROUP BY)子句,则无法保证返回行的顺序。

If you need the rows returned in a particular sequence, you should include an ORDER BY on the outermost query. In a lot of use cases, we can just use an ORDER BY on the outermost query to satisfy the results.

如果需要以特定顺序返回的行,则应在最外层的查询中包含ORDER BY。在很多用例中,我们可以在最外层的查询中使用ORDER BY来满足结果。

However, if you have a use case where you need all the rows from the first query returned before all the rows from the second query, one option is to include an extra discriminator column in each of the queries. For example, add ,'a' AS src in the first query, ,'b' AS src to the second query.

但是,如果您有一个用例,您需要在第二个查询的所有行之前返回的第一个查询中的所有行,则一个选项是在每个查询中包含一个额外的鉴别器列。例如,在第一个查询中添加'a'AS src,将'b'AS src添加到第二个查询。

Then the outermost query could include ORDER BY src, name, to guarantee the sequence of the results.

然后最外面的查询可以包括ORDER BY src,name,以保证结果的顺序。

---

FOLLOWUP

In your original query, the ORDER BY in your queries is discarded by the optimizer; since there is no ORDER BY applied to the outer query, MySQL is free to return the rows in whatever order it wants.

在原始查询中,优化程序会丢弃查询中的ORDER BY;由于没有ORDER BY应用于外部查询,MySQL可以按照自己想要的顺序*返回行。

The "trick" in query in my answer (above) is dependent on behavior that may be specific to some versions of MySQL.

我的答案(上面)中的查询中的“技巧”取决于某些版本的MySQL特有的行为。

Test case:

populate tables

CREATE TABLE foo2 (id INT PRIMARY KEY, role VARCHAR(20)) ENGINE=InnoDB;
CREATE TABLE foo3 (id INT PRIMARY KEY, role VARCHAR(20)) ENGINE=InnoDB;

INSERT INTO foo2 (id, role) VALUES 
  (1,'sam'),(2,'frodo'),(3,'aragorn'),(4,'pippin'),(5,'gandalf');
INSERT INTO foo3 (id, role) VALUES 
  (1,'gimli'),(2,'boromir'),(3,'elron'),(4,'merry'),(5,'legolas');

query

SELECT a.*
  FROM ( SELECT s.id, s.role
           FROM foo2 s
          ORDER BY s.role
       ) a
 UNION ALL
SELECT b.*
  FROM ( SELECT t.id, t.role
           FROM foo3 t
          ORDER BY t.role
       ) b

resultset returned

    id  role     
 ------  ---------
      3  aragorn  
      2  frodo    
      5  gandalf  
      4  pippin   
      1  sam      
      2  boromir  
      3  elron    
      1  gimli    
      5  legolas  
      4  merry    

The rows from foo2 are returned "in order", followed by the rows from foo3, again, "in order".

来自foo2的行按顺序返回,然后是来自foo3的行,再次是“按顺序”。

Note (again) that this behavior is NOT guaranteed. (The behavior we observer is a side effect of how MySQL processes inline views (derived tables). This behavior may be different in versions after 5.5.)

请注意(再次)不保证此行为。 (我们观察者的行为是MySQL如何处理内联视图(派生表)的副作用。在5.5之后的版本中,这种行为可能会有所不同。)

If you need the rows returned in a particular order, then specify an ORDER BY clause for the outermost query. And that ordering will apply to the entire resultset.

如果需要按特定顺序返回的行,请为最外面的查询指定ORDER BY子句。该顺序将适用于整个结果集。

As I mentioned earlier, if I needed the rows from the first query first, followed by the second query, I would include a "discriminator" column in each query, and then include the "discriminator" column in the ORDER BY clause. I would also do away with the inline views, and do something like this:

正如我前面提到的,如果我首先需要第一个查询中的行,然后是第二个查询,我会在每个查询中包含一个“discriminator”列,然后在ORDER BY子句中包含“discriminator”列。我也会取消内联视图,并执行以下操作:

SELECT s.id, s.role, 's' AS src
  FROM foo2 s
 UNION ALL
SELECT t.id, t.role, 't' AS src
  FROM foo3 t
 ORDER BY src, role

#2

You just use one ORDER BY at the very end.

你只需在最后使用一个ORDER BY。

The Union turns two selects into one logical select. The order-by applies to the entire set, not to each part.

联盟将两个选择转换为一个逻辑选择。 order-by适用于整个集合,而不适用于每个部分。

Don't use any parens either. Just:

也不要使用任何parens。只是:

SELECT 1 as Origin, blah blah FROM foo WHERE x
UNION ALL
SELECT 2 as Origin, blah blah FROM foo WHERE y
ORDER BY Origin, z

#3

Don't use ORDER BY in an individual SELECT statement inside a UNION, unless you're using LIMIT with it.

不要在UNION内的单个SELECT语句中使用ORDER BY,除非您使用LIMIT。

The MySQL docs on UNION explain why (emphasis mine):

关于UNION的MySQL文档解释了为什么(强调我的):

To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

要将ORDER BY或LIMIT应用于单个SELECT,请将子句放在包含SELECT的括号内:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

However, 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.

但是,对单个SELECT语句使用ORDER BY并不意味着行在最终结果中出现的顺序,因为UNION默认生成一组无序行。因此,在此上下文中使用ORDER BY通常与LIMIT结合使用,因此它用于确定要为SELECT检索的所选行的子集,即使它不一定影响这些行中的那些行的顺序。最终的UNION结果。如果在SELECT中没有LIMIT出现ORDER BY,它将被优化掉,因为它无论如何都不会产生任何影响。

To use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one. The following example uses both clauses:

要使用ORDER BY或LIMIT子句对整个UNION结果进行排序或限制,请将各个SELECT语句括起来,并将ORDER BY或LIMIT置于最后一个之后。以下示例使用两个子句:

(SELECT a FROM t1 WHERE a=10 AND B=1)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;

It seems like an ORDER BY clause like the following will get you what you want:

看起来像下面的ORDER BY子句会得到你想要的东西:

ORDER BY user_id, name

#4

      (SELECT id, user_id, other_id, name
         FROM tablename
        WHERE user_id = 123
          AND user_in IN (...))
UNION ALL
      (SELECT id, user_id, other_id, name
         FROM tablename
        WHERE user_id = 456
          AND user_id NOT IN (...)))
 ORDER BY name

You can also simplify this query:

您还可以简化此查询:

SELECT id, user_id, other_id, name
  FROM tablename
 WHERE (user_id = 123 AND user_in IN (...))
    OR (user_id = 456 AND user_id NOT IN (...))