T-SQL筆記37_除非同時指定了 TOP、OFFSET 或 FOR XML，否則 ORDER BY 子句在檢視表、內嵌函式、衍生資料表、子查詢及通用資料表運算式中均為無效。

 

最近因應效率問題

在改寫資料分頁

而發現子查詢中不能有order by

這時只要把 select * 

調整為

TOP 100 PERCENT

Before

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

DECLARE @PageIndex INT = 1 DECLARE @PageSize INT = 20 SELECT * FROM ( SELECT * ,ROW_NUMBER() OVER ( ORDER BY ROWID ) AS _RowId FROM TmpProductItem WITH (NOLOCK) WHERE 1 = 1 AND OnlineRoomId = '17' ORDER BY ISNull(Qty, 0) DESC ,Id ) AS vw WHERE _RowId BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex * @PageSize

After

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

DECLARE @PageIndex INT = 1 DECLARE @PageSize INT = 20 SELECT top 100 percent * FROM ( SELECT top 100 percent * ,ROW_NUMBER() OVER ( ORDER BY ROWID ) AS _RowId FROM TmpProductItem WITH (NOLOCK) WHERE 1 = 1 AND OnlineRoomId = '17' ORDER BY ISNull(Qty, 0) DESC ,Id ) AS vw WHERE _RowId BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex * @PageSize

備註:

--返回符合條件的100%的記錄，即所有符合條件的記錄

SELECT TOP 100 PERCENT *

--返回符合條件的100條記錄，即只返回符合條件的100條記錄

SELECT TOP 100 *

Ref:

https://bbs.csdn.net/topics/90203719

http://deanma.blogspot.com/2015/01/ms-sql-view-orderby.html

https://blog.csdn.net/weixin_34044273/article/details/93512047?spm=1035.2023.3001.6557&utm_medium=distribute.pc_relevant_bbs_down_v2.none-task-blog-2~default~ESQUERY~Rate-1-93512047-bbs-90203719.pc_relevant_bbs_down_v2_opensearchbbsnew&depth_1-utm_source=distribute.pc_relevant_bbs_down_v2.none-task-blog-2~default~ESQUERY~Rate-1-93512047-bbs-90203719.pc_relevant_bbs_down_v2_opensearchbbsnew

https://blog.csdn.net/kitsmiler/article/details/1746744?spm=1035.2023.3001.6557&utm_medium=distribute.pc_relevant_bbs_down_v2.none-task-blog-2~default~ESQUERY~Rate-3-1746744-bbs-90203719.pc_relevant_bbs_down_v2_opensearchbbsnew&depth_1-utm_source=distribute.pc_relevant_bbs_down_v2.none-task-blog-2~default~ESQUERY~Rate-3-1746744-bbs-90203719.pc_relevant_bbs_down_v2_opensearchbbsnew