如何在UNION中使用ORDER BY

如果需要按特定顺序返回的行，则应在最外面的查询中包含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 ALLSELECT 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 ALLSELECT t.id, t.role, 't' AS src FROM foo3 t ORDER BY src, roleI 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, nameFROM tablenameWHERE user_id = 123 AND user_in IN (...)ORDER BY name)UNION ALL(SELECT id, user_id, other_id, nameFROM tablenameWHERE 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:12312345Thank you very much! 解决方案 Something like this should work in MySQL:SELECT a.* FROM ( SELECT ... FROM ... ORDER BY ... ) a UNION ALLSELECT b.* FROM ( SELECT ... FROM ... ORDER BY ... ) bNote 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.FOLLOWUPIn 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 tablesCREATE 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');querySELECT a.* FROM ( SELECT s.id, s.role FROM foo2 s ORDER BY s.role ) a UNION ALLSELECT b.* FROM ( SELECT t.id, t.role FROM foo3 t ORDER BY t.role ) bresultset returned id role ------ --------- 3 aragorn 2 frodo 5 gandalf 4 pippin 1 sam 2 boromir 3 elron 1 gimli 5 legolas 4 merryThe 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 ALLSELECT t.id, t.role, 't' AS src FROM foo3 t ORDER BY src, role 这篇关于如何在UNION中使用ORDER BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！