sql - 使用联合的不同 order by

Question

我想编写一个查询，例如

    select top 10 * from A
    order by price
    union
    select top 3 * from A 
    order by price

或者类似的东西

    select top 10 * from A
    where name like '%smt%'
    order by price
    union
    select top 3 * from A
    where name not like '%smt%'
    order by price 

你能帮我吗？

Answer 1

这应该有效:

SELECT * 
FROM (SELECT TOP 10 A.*, 0 AS Ordinal
      FROM A
      ORDER BY [Price]) AS A1

UNION ALL

SELECT * 
FROM (SELECT TOP 3 A.*, 1 AS Ordinal
      FROM A
      ORDER BY [Name]) AS A2

ORDER BY Ordinal

来自MSDN :

In a query that uses UNION, EXCEPT, or INTERSECT operators, ORDER BY is allowed only at the end of the statement. This restriction applies only to when you specify UNION, EXCEPT and INTERSECT in a top-level query and not in a subquery.

已编辑:要强制执行订单，您需要将 ORDER BY 应用于外部查询。我已向两个查询添加了一个常量值列。