刚发现这个语句竟然还是不对的,出现如下错误:The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified. 必须要写成SELECT TOP 100 PERCENT ... 搞不懂到底为什么
在view使用order by 必须要和top一起使用才可以CREATE View [NewView] AS SELECT top n * FROM Table1 ORDER BY ID
select text from syscomments where id=object_id('视图名')
declare @viewtext varchar(8000) selecT @viewtext=text from syscomments where id=object_id('VW_BRFYMXK01') select substring(@viewtext,charindex('#$#',replace(@viewtext,'order by','#$#'))+8,100)
1.视图语句应为 CREATE View [NewView] AS SELECT top 100 percent * FROM Table1 ORDER BY ID 2.查看 sp_helptext NewView
declare @viewText as varchar(1000) select @viewText = convert(varchar(1000),[text]) from syscomments where id = object_id('[NewView]') print replace(replace(right(@viewText,len(@viewText)- charindex('ORDER BY',@viewText) - len('ORDER BY')),char(32),''),',',char(13))--取出来后,去掉空格,替换order by id,name-->逗号,再根据需求处理
selecT @viewtext=text from syscomments where id=object_id('VW_BRFYMXK01')
select substring(@viewtext,charindex('#$#',replace(@viewtext,'order by','#$#'))+8,100)
CREATE View [NewView] AS SELECT top 100 percent * FROM Table1 ORDER BY ID
2.查看
sp_helptext NewView
select @viewText = convert(varchar(1000),[text]) from syscomments where id = object_id('[NewView]')
print replace(replace(right(@viewText,len(@viewText)- charindex('ORDER BY',@viewText) - len('ORDER BY')),char(32),''),',',char(13))--取出来后,去掉空格,替换order by id,name-->逗号,再根据需求处理