我想写一个像这样的查询
- 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.
