如何在UNION中使用ORDER BY [英] How to use ORDER BY inside UNION

问题描述

我想对每个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
)

只是要清楚:我需要两个这样的有序列表，而不是一个:

1 2个 3 1个 2个 3 4 5

非常感谢！

解决方案

在MySQL中类似的方法应该起作用:

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

但是请注意，在最外层的查询中缺少 ORDER BY (或GROUP BY)子句，将保证 NOT 的行返回顺序.

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

但是，如果您有一个用例，需要在第一个查询中返回的所有行都在第二个查询中的所有行之前返回，则一个选择是在每个查询中都包含一个额外的区分符列.例如，在第一个查询中添加 ,'a' AS src ，在第二个查询中添加 ,'b' AS src .

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

---

关注

在您的原始查询中，查询中的ORDER BY被优化程序丢弃；由于没有将ORDER BY应用于外部查询，因此MySQL可以随意以任意顺序返回行.

我的答案(上面)中查询的技巧"取决于特定于某些MySQL版本的行为.

测试用例:

填充表格

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');

查询

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

返回结果集

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

foo2中的行将按顺序"返回，然后再次返回foo3中的行，按顺序".

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

如果需要按特定顺序返回的行，则为最外面的查询指定 ORDER BY 子句.并且该排序将应用于整个结果集.

如前所述，如果我首先需要第一个查询的行，然后是第二个查询的行，则我将在每个查询中包含一个"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

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:

(
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

Thank you very much!

解决方案

Something like this should work in 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.

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.

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.

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

---

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.

The "trick" in query in my answer (above) is dependent on behavior that may be specific to some versions of 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".

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

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.

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:

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

这篇关于如何在UNION中使用ORDER BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！