SQL ROW_NUMBER() ..在 x 和 y 之间中断 内部连接

Question

我有这个内部连接语句，它在大多数情况下都有效，但是当我添加自定义分页时，连接会返回一个空集。 任何建议将不胜感激。

这将返回我期望的结果

SELECT [Id]
,[Title]
FROM 
(SELECT [Id]
,[Title]
,ROW_NUMBER() OVER(ORDER BY id) as RowNum from Art) as e
INNER JOIN [ArtCat] p ON e.Id = p.ArtId 
WHERE 
p.CatId = @CategoryNum

这将返回一个空集

SELECT [Id]
,[Title]
FROM 
(SELECT [Id]
,[Title]
,ROW_NUMBER() OVER(ORDER BY id) as RowNum from Art) as e
INNER JOIN [ArtCat] p ON e.Id = p.ArtId 
WHERE 
p.CatId = @CategoryNum
AND  RowNum BETWEEN @startIndex AND (@startIndex + @pageSize) 

提前感谢！

Answer 1

格式化有点难以阅读，但语法很好，为什么不检查 rownum 的边界：

SELECT MIN(RowNum),MAX(RowNum)          
FROM (SELECT [Id]
            ,[Title]
            ,ROW_NUMBER() OVER(ORDER BY id) as RowNum 
      FROM Art
      ) as e
INNER JOIN [ArtCat] p ON e.Id = p.ArtId 
WHERE p.CatId = @CategoryNum

很有可能你应该在限制p.CatId = @CategoryNum后使用RowNum

猜测这个有效：

SELECT [Id]
      ,[Title]
FROM (SELECT  [Id]
             ,[Title]
             ,ROW_NUMBER() OVER(ORDER BY id) as RowNum         
      FROM (SELECT [Id]
                  ,[Title]
            FROM Art
            ) as e
      INNER JOIN [ArtCat] p ON e.Id = p.ArtId 
      WHERE p.CatId = @CategoryNum
     )sub
WHERE RowNum BETWEEN @startIndex AND (@startIndex + @pageSize) 

Answer 2

山羊CO你真的很有帮助！！ 谢谢。 我们最终使用的代码是。

SELECT
       ROW_NUMBER() OVER(ORDER BY id) as e.RowNum  
       ,e.[Id]
      ,e.[Title]
FROM (SELECT  
          [Id]
         ,[Title]
         ,ROW_NUMBER() OVER(ORDER BY id) as RowNum         
  FROM 
     (SELECT
           ROW_NUMBER() OVER(ORDER BY id) as RowNum
           ,[Id]
           ,[Title]
           FROM Art
              INNER JOIN [ArtCat] p ON e.Id = p.ArtId 
       WHERE p.CatId = @CategoryNum
     ) as e
WHERE 
  e.RowNum BETWEEN @startIndex AND (@startIndex + @pageSize) 

预先过滤结果（就像您一样），但在外部表上制作另一行以真正组织结果。

我的同事也抱怨我的格式。 医 管 局