T-SQL 跳过存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5620758/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
T-SQL Skip Take Stored Procedure
提问by Pete Davies
I don't seem to be having much luck on this site, still forever the optimist, I will keep trying. I have two tables, Journals and ArticleCategories that are joined using the this query:
我在这个网站上似乎不太走运，仍然永远是乐观主义者，我会继续努力。我有两个表， Journals 和 ArticleCategories 使用此查询连接：
SELECT Journals.JournalId,
Journals.Year,
Journals.Title,
ArticleCategories.ItemText
FROM Journals
LEFT OUTER JOIN ArticleCategories
ON Journals.ArticleCategoryId = ArticleCategories.ArticleCategoryId
Can anyone tell me how I can re-write this make it into a Skip, Take query. In other words, I want to it skip the first n records and then take the next n. I think ROW_NUMBER is involved somewhere but I cannot work out how to use it in this case.
谁能告诉我如何将其重写为跳过，接受查询。换句话说，我希望它跳过前 n 条记录，然后取下 n 条记录。我认为 ROW_NUMBER 涉及某个地方，但在这种情况下我无法弄清楚如何使用它。
I suspect the reason why don't have much luck is that I find it difficult to explain what I am trying to do. If my question is not clear, please do not hesitate to tell me where I am going wrong and I will gladly try again. Perhaps I should also mention that I am trying to put this in a stored procedure. Many Thanks. Many thanks,
我怀疑运气不佳的原因是我发现很难解释我想要做什么。如果我的问题不清楚，请不要犹豫告诉我哪里出错了，我很乐意再试一次。也许我还应该提到我正在尝试将其放入存储过程中。非常感谢。非常感谢，
回答by Martin Smith
For 2005 / 2008 / 2008 R2
对于 2005 / 2008 / 2008 R2
;WITH cte AS
(
SELECT Journals.JournalId,
Journals.Year,
Journals.Title,
ArticleCategories.ItemText,
ROW_NUMBER() OVER
(ORDER BY Journals.JournalId,ArticleCategories.ItemText) AS RN
FROM Journals LEFT OUTER JOIN
ArticleCategories
ON Journals.ArticleCategoryId = ArticleCategories.ArticleCategoryId
)
SELECT JournalId,
Year,
Title,
ItemText
FROM cte
WHERE RN BETWEEN 11 AND 20
For 2012 this is simpler
对于 2012 年，这更简单
SELECT Journals.JournalId,
Journals.Year,
Journals.Title,
ArticleCategories.ItemText
FROM Journals
LEFT OUTER JOIN ArticleCategories
ON Journals.ArticleCategoryId = ArticleCategories.ArticleCategoryId
ORDER BY Journals.JournalId,
ArticleCategories.ItemText
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY
回答by Adam Hey
In addition to @Martin Smith's correct answer - when using a GROUP BY, you can't use OFFSET-FETCHwithout an ORDER BY:
除了@Martin Smith 的正确答案 - 使用 a 时GROUP BY，您不能在没有 的OFFSET-FETCH情况下使用ORDER BY：
GROUP BY [cols]
ORDER BY [col] ASC|DESC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY
The following gives "incorrect syntaxt near 'OFFSET'" :
以下给出“'OFFSET'附近的错误语法”：
GROUP BY [cols]
--ORDER BY [col] ASC|DESC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY
