为什么我的两个查询的联合没有正确排序?

 (SELECT `size`, `type` FROM `table` WHERE `type`='a' ORDER BY `size` ASC)
UNION ALL 
(SELECT `size`,`type` FROM `table` WHERE `type`='b' ORDER BY `size` DESC)

Why isn't this query working as I hope? It separates the result array into types 'a' and then types 'b', but within the type 'a' results, they are not ordered by size (size is a positive bigint).

为什么这个查询不像我希望的那样工作?它将结果数组分离为类型'a'，然后类型'b'，但在类型'a'结果中，它们不是按大小排序的(大小是正的bigint)。

Any suggestions? :)

有什么建议吗?:)

1 个解决方案

#1

The results of a query are not ordered, unless you use an order by clause (or in MySQL a group by clause). Try this to get what you want:

查询的结果不是有序的，除非您使用order by子句(或者在MySQL中使用group by子句)。试试这个来得到你想要的:

(SELECT `size`, `type` FROM `table` WHERE `type`='a')
UNION ALL 
(SELECT `size`,`type` FROM `table` WHERE `type`='b')
order by type,
         (case when `type` = 'a' then size end) asc,
         (case when `type` = 'b' then size end) desc;

Ordering clauses on subqueries are generally ignored (the exception is when you have limit). And, even when not ignored may have not effect on the outer query.

子查询上的排序子句通常被忽略(例外是当您有限制时)。而且，即使没有被忽略，也不会对外部查询产生影响。

Actually, forget the union all entirely:

实际上，完全忘记工会:

select size, `type`
from table
where type in ('a', 'b')
order by type,
         (case when `type` = 'a' then size end) asc,
         (case when `type` = 'b' then size end) desc;

The first type in the order by clause is unnecessary, but I do think that it makes the intention of the ordering clearer.

order by子句中的第一个类型是不必要的，但是我认为它使订购的意图更加清晰。

#1