SQL Union的Order效果实现

 

REF： http://www.issociate.de/board/goto/721148/SQL_Union_with_ORDER_BY.html

引用一个论坛的解决之道。原理是，让搜索的结果先“形成”一张新表以形成所需要的排序。而union则是默认的语法，把多个结果集合并在一起。嗯，SQL语句是编程语言中比高级语言更高级的语言，因为他更接近于自然语言的缘故吗？Don't Know ^_^

In a UNION query, you can only have one ORDER BY clause, at the end, and
it will apply to the whole result, not to one of the UNION'ed SELECT
statements.

One possible workaround is to use derived tables, something like this:

SELECT ....
FROM (SELECT TOP 1 ...
FROM ...
ORDER BY ...) AS x
UNION ALL
SELECT ....
FROM (SELECT TOP 4 ...
FROM ...
ORDER BY ... DESC) AS y