今天写了个SQL语句
select * from (
select … from A?
union
?select …from B order by B.xx
) as Tmp
SQL查询分析器中老是报错：The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
而单独执行select … from A?
union
?select …from B order by B.xx语句时是可以的。
后来分析原因如下：
sql中建view是不能用order by字句的，即：
如果把该查询语句建成视图
? create view v_test
? as
? select * from sysobjects order by name
? 会提示出错:
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
而在嵌套查询中，系统会自动为select … from A?
union
?select …from B order by B.xx语句建一个view，里面有order by字句，当然会报错了，去掉order by后，运行成功！