sql – 使用union的不同顺序

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

你能帮我么？

解决方法

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应用于外部查询所需的顺序.我为两个查询添加了一个常量值列.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!