""OFFSET"附近的语法不正确" Modift SQL通讯2012年至2008年 [英] "Incorrect syntax near '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的查询?

How can I modify my query for sql server 2008?

还有一个问题.如何编写用于列出页面的存储过程?这是我的完整代码 http://codepaste.net/gq5n6c

One more question. How can I write a stored procedure for listing pages? Here is my full of code http://codepaste.net/gq5n6c

答案: http://codepaste.net/jjrkqr

推荐答案

在注释中发现错误的原因是因为SQL Server 2008不支持它.您可以尝试根据SQL Server 2012更改查询.

As found out in the comments the reason for the error is because of the fact that SQL Server 2008 does not support it. You may try to change the query according to 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是什么.我将其视为某个常量，或者您可以声明它，然后为其设置值):-

Try this (As you have not mentioned what is @page. I am taking it as some constant or may be you can declare it and then set the value for it):-

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

这篇关于""OFFSET"附近的语法不正确" Modift SQL通讯2012年至2008年的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！