sql-server – ORDER BY子句在视图,内联函数,派生表,子查询和公

发布时间：2020-12-12 16:10:29 所属栏目：MsSql教程来源：网络整理

导读：我得到“ORDER BY子句在视图,内联函数,派生表,子查询和公用表表达式中无效,除非还指定了TOP或FOR XML.错误与以下代码.我最初有两张桌子,ADSAREAS类别.当我删除CATEGORIES表时,我开始收到此错误. Select Case SIDX Case "ID" : SQLCONT1 = " AdsAreasID" Case

我得到“ORDER BY子句在视图,内联函数,派生表,子查询和公用表表达式中无效,除非还指定了TOP或FOR XML.错误与以下代码.我最初有两张桌子,ADSAREAS&类别.当我删除CATEGORIES表时,我开始收到此错误.

Select Case SIDX  
     Case "ID" : SQLCONT1 = " AdsAreasID"
     Case "Page" : SQLCONT1 = " AdsAreasName"
     Case Else : SQLCONT1 = " AdsAreasID"  
End Select   
Select Case SORD  
     Case "asc" : SQLCONT2 = " ASC"
     Case "desc" : SQLCONT2 = " DESC"
     Case Else : SQLCONT2 = " ASC"  
End Select   
''# search feature --->
Select Case SEARCHFIELD  
     Case "ID" : SQLSFIELD = "AND AdsAreasID"
     Case "Ads Areas" : SQLSFIELD = "AND AdsAreasName"
     Case Else : SQLSFIELD = ""  
End Select  
Select Case SEARCHOPER  
     Case "eq" : SQLSOPER = " = " & SEARCHSTRING
     Case "ne" : SQLSOPER = " <> " & SEARCHSTRING
     Case "lt" : SQLSOPER = " <" & SEARCHSTRING
     Case "le" : SQLSOPER = " <= " & SEARCHSTRING    
     Case "gt" : SQLSOPER = " >" & SEARCHSTRING
     Case "ge" : SQLSOPER = " >= " & SEARCHSTRING
     Case "bw" : SQLSOPER = " LIKE '" & SEARCHSTRING & "%' "
     Case "ew" : SQLSOPER = " LIKE '%" & SEARCHSTRING & "' "
     Case "cn" : SQLSOPER = " LIKE '%" & SEARCHSTRING & "%' "
     Case Else : SQLSOPER = ""  
End Select  
''# search feature --->

SQL = "SELECT * FROM ( SELECT A.AdsAreasID,A.AdsAreasName,ROW_NUMBER() OVER (ORDER BY A.AdsAreasID) As Row"
SQL = SQL & " FROM ADSAREAS A"
SQL = SQL & " WHERE Row > ("& RecordsPageSize - RecordsPerPage &") AND Row <= ("& RecordsPageSize &") ORDER BY" & SQLCONT1 & SQLCONT2
Set objXML = objConn.Execute(SQL)

解决方法

您将ORDER BY子句移动到内部查询以重写它.在WHERE子句之后添加括号(和标识符),以便ORDER BY适用于外部SELECT.

SQL = "SELECT * FROM ( SELECT A.AdsAreasID,ROW_NUMBER() OVER (ORDER BY A.AdsAreasID) As Row"
SQL = SQL & " FROM ADSAREAS A"
SQL = SQL & " WHERE Row > ("& RecordsPageSize - RecordsPerPage &") AND Row <= ("& RecordsPageSize &")) inner ORDER BY" & SQLCONT1 & SQLCONT2

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!