代码之家 › 专栏 › 技术社区 › E.Meir

在只选择PageSize demand之前计算表中的行数

tsql sql-server sql

E.Meir · 技术社区 · 6 年前

我试图得到表中的总行数 tblEmployees500k 在选择行数之前 @PageNumber

ALTER PROCEDURE [dbo].[spPagination] -- ORDER BY EmployeeID
  @PageNumber INT,
  @PageSize   INT
AS
BEGIN
  SET NOCOUNT ON;

    SELECT      Emp.id ,Emp.[firstName], Emp.[lastName], Emp.[salary], Emp.[startDateWork], Emp.age, Count(1) AS [RecordCount] 
    FROM        [dbo].[tblEmployees500k]    Emp
    ORDER BY    id      
    OFFSET      @PageSize * (@PageNumber - 1) ROWS
    FETCH NEXT  @PageSize ROWS ONLY OPTION (RECOMPILE);
END

但是 Count(1) AS [RecordCount] 引起错误

“dbo.tblEmployees500k.id”列在选择列表中无效,因为它不包含在聚合函数或GROUP BY子句中。

4 回复 | 直到 6 年前

D-Shih 6 年前

使用聚合函数时,需要在 group by

你可以试着用 Count(*) over(partition by Emp.id order by Emp.id) count(1)

SELECT      Emp.id ,Emp.[firstName], Emp.[lastName], Emp.[salary], Emp.[startDateWork], Emp.age, Count(*) over(partition by Emp.id order by Emp.id) AS [RecordCount] 
FROM        [dbo].[tblEmployees500k]    Emp       
OFFSET      @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT  @PageSize ROWS ONLY OPTION (RECOMPILE);

编辑

如果要计算表中的所有行,可以尝试使用子查询获取总计 count

SELECT      Emp.id ,Emp.[firstName], Emp.[lastName], Emp.[salary], Emp.[startDateWork], Emp.age, (SELECT COUNT(*) FROM [dbo].[tblEmployees500k]) AS [RecordCount] 
FROM        [dbo].[tblEmployees500k]    Emp       
OFFSET      @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT  @PageSize ROWS ONLY OPTION (RECOMPILE);

或者你可以用 CROSS JOIN 才能成功。

SELECT      Emp.id ,Emp.[firstName], Emp.[lastName], Emp.[salary], Emp.[startDateWork], Emp.age,t1.cnt AS [RecordCount] 
FROM        
    [dbo].[tblEmployees500k]    Emp 
CROSS JOIN 
    (SELECT COUNT(*) cnt FROM [dbo].[tblEmployees500k]) t1      
OFFSET      @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT  @PageSize ROWS ONLY OPTION (RECOMPILE);

Gordon Linoff 6 年前

使用窗口功能:

Count(*) over () AS [RecordCount]

( count(1) 和 count(*)

Yogesh Sharma 6 年前

你也可以使用 cross join :

 SELECT Emp.id, Emp.[firstName], Emp.[lastName], 
        Emp.[salary], Emp.[startDateWork], Emp.age, cnt.[RecordCount]
 FROM [dbo].[tblEmployees500k] Emp CROSS JOIN
      (SELECT COUNT(*) AS [RecordCount] FROM [dbo].[tblEmployees500k]) cnt;

Ruslan Tolkachev 6 年前

ALTER PROCEDURE [dbo].[spPagination] -- ORDER BY EmployeeID
  @PageNumber INT,
  @PageSize   INT
AS
BEGIN
    SET NOCOUNT ON;

DECLARE @NumOfRows int = (select count(*) from [dbo].[tblEmployees500k] );

SELECT      Emp.id ,Emp.[firstName], Emp.[lastName], Emp.[salary], Emp.[startDateWork], Emp.age, @NumOfRows AS [RecordCount] 
FROM        [dbo].[tblEmployees500k]    Emp
ORDER BY    id      
OFFSET      @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT  @PageSize ROWS ONLY OPTION (RECOMPILE);
END

这样它只能算一次。