``在'OFFSET'附近的语法不正确'.modift sql comm 2012 to 2008
我为此列出了问题
SELECT q.qTitle, q.qDescription, q.qCreatedOn, u.uCode, u.uFullname, qcat.qcatTitle, q.qId, q.qStatus
FROM tblQuestion AS q INNER JOIN tblUser AS u
ON q.uId = u.uId INNER JOIN tblQuestionCategory AS qcat
ON q.qcatId = qcat.qcatId
WHERE (q.qStatus = 1)
ORDER BY q.qCreatedOn DESC
OFFSET @page*10 ROWS FETCH NEXT 10 ROWS ONLY
但是我的服务器有问题,
Incorrect syntax near 'OFFSET'.
Invalid usage of the option NEXT in the FETCH statement.
如何修改SQL Server 2008的查询?
还有一个问题。如何编写用于列出页面的存储过程?这是我的完整代码http://codepaste.net/gq5n6c
答案:http://codepaste.net/jjrkqr
-
如注释中所发现的,导致该错误的原因是由于SQL Server 2008不支持它。您可以尝试根据SQL Server 2012更改查询。
像这样的东西:
SELECT column1 FROM ( SELECT column1, ROW_NUMBER() OVER (ORDER BY column_id) AS x FROM mytable ) AS tbl WHERE tbl.x BETWEEN 20 AND 30
在您的代码中:-
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY q.qId) AS rownumber FROM tblQuestion AS q INNER JOIN tblUser AS u ON q.uId = u.uId INNER JOIN tblQuestionCategory AS qcat ON q.qcatId = qcat.qcatId ) as somex WHERE somex.rownumber BETWEEN 11 AND 20
问题是因为您尚未定义
@page
。试试这个(因为您还没有提到什么
@page
。我将其视为某个常量,或者您可以声明它,然后为其设置值):-declare @page int set @page = 5 // You may set any value here. SELECT q.qTitle, q.qDescription, q.qCreatedOn, u.uCode, u.uFullname, qcat.qcatTitle, q.qId, q.qStatus FROM tblQuestion AS q INNER JOIN tblUser AS u ON q.uId = u.uId INNER JOIN tblQuestionCategory AS qcat ON q.qcatId = qcat.qcatId WHERE (q.qStatus = 1) ORDER BY q.qCreatedOn DESC OFFSET (@page*10) ROWS FETCH NEXT 10 ROWS ONLY