T-SQL 跳过存储过程

2022-01-04 00:00:00 sql tsql sql-server pagination

我在这个网站上似乎不太走运，仍然永远是乐观主义者，我会继续努力.我有两个表， Journals 和 ArticleCategories 使用此查询连接:

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: 

SELECT Journals.JournalId,
       Journals.Year,
       Journals.Title,
       ArticleCategories.ItemText
FROM   Journals
       LEFT OUTER JOIN ArticleCategories
         ON Journals.ArticleCategoryId = ArticleCategories.ArticleCategoryId

谁能告诉我如何将其重写为跳过，接受查询.换句话说，我希望它跳过前 n 条记录，然后取下 n 条记录.我认为 ROW_NUMBER 涉及某个地方，但我不知道在这种情况下如何使用它.

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.

我怀疑运气不佳的原因是我发现很难解释我想要做什么.如果我的问题不清楚，请随时告诉我我哪里出错了，我很乐意再试一次.也许我还应该提到我正在尝试将其放入存储过程中.非常感谢.非常感谢，

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,

推荐答案

对于 2005/2008/2008 R2

For 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

对于 2012 来说，这更简单

For 2012 this is simpler

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

相关文章