【sqlServer】Order By 报错：The ORDER BY clause is invalid in views, inline functions, derived tables

原创于 2016-10-25 18:28:32 发布 · 7.7k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql server #order-by

SqlServer 专栏收录该内容

59 篇文章

订阅专栏

本文探讨了在SQLServer中使用ORDERBY子句时遇到的限制及其解决方法。通过添加TOP子句绕过了ORDERBY在视图、内联函数等场景下的限制，并对比了Oracle中的实现差异。

--【sqlserver】报错
with tp as 
(select id,end_date,gua_com 
 from testbzm order by end_date)

select * from tp
/*
--报错
消息 1033，级别 15，状态 1，第 3 行
The ORDER BY clause is invalid in views, inline functions, derived tables, 
subqueries, and common table expressions, unless TOP or FOR XML is also specified.
*/


--------------------利用TOP绕过报错
with tp as 
(select top 100 percent /*返回符合条件的100%的记录，即所有符合条件的记录*/
    id,end_date,gua_com 
 from testbzm order by end_date)

select * from tp

--另：【ORACLE】不报错
insert into testbzm values(1,'aa');
insert into testbzm values(2,'bb');
insert into testbzm values(3,'cc');
insert into testbzm values(4,'dd');

commit;

select * from testbzm;

--------
with tp as (
select i,v from testbzm order by v
)

select * from tp;