EXEC('SELECT A.* FROM (
select r.ResourceID as ResourceID, gl.GuideID,r.Popularity,r.Title,r.CreateDate,
r.BrowseTimes,r.LikeTimes,r.Recommended, r.SourceType,g.GuideName, ROW_NUMBER() OVER (order by a.SourceType)
AS RowNumber
from [Guide^Resouce] as gr
left join [dbo].[Resouce] as r
on gr.ResouceID =r.ResourceID
left join dbo.guide as g
on gr.GuideID=g.GuideID
left join GuideLibrarian as gl
on gr.GuideID=gl.GuideID
WHERE 1=1 and gl.LibrarianId=1 and r.ResourceID is not NULL and g.GuideID is not null
order by a.SourceType desc
) A
WHERE RowNumber BETWEEN 1 AND 20 ')
我必须在排序之后 获取前20条
消息 1033，级别 15，状态 1，第 14 行
除非另外还指定了 TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
请问这个应该怎么改啊
------解决方案--------------------
EXEC('SELECT A.* FROM (
select top 99.999 percent r.ResourceID as ResourceID, gl.GuideID,r.Popularity,r.Title,r.CreateDate,
r.BrowseTimes,r.LikeTimes,r.Recommended, r.SourceType,g.GuideName, ROW_NUMBER() OVER (order by a.SourceType)
AS RowNumber
from [Guide^Resouce] as gr
left join [dbo].[Resouce] as r
on gr.ResouceID =r.ResourceID
left join dbo.guide as g
on gr.GuideID=g.GuideID
left join GuideLibrarian as gl
on gr.GuideID=gl.GuideID
WHERE 1=1 and gl.LibrarianId=1 and r.ResourceID is not NULL and g.GuideID is not null
order by a.SourceType desc
) A
WHERE RowNumber BETWEEN 1 AND 20 ')
------解决方案--------------------
你的语句用ORDER BY排序了
前面需要用TOP语句 不然就报错。