如何在单查询中返回指定列与总行数并实现分页？

DevOps 小满

2026-8-17

问题描述

感谢Tim Biegeleisen指出我问题中的笔误。我实际代码里的limit和offset都设为50，直到你指出我才发现。

我希望限制查询返回的行数，但同时获取表中符合条件的总行数。我尝试了以下查询：

SELECT 
    first,
    last_name,
    date_joined,
    age,
    COUNT(*) AS num_people
FROM
    foo_table
WHERE
    city = 'bar'
ORDER BY date_joined DESC
LIMIT 0, 50;

但该查询仅返回一行看似随机的数据。将COUNT(*)改为5 AS num_people后，能正确返回所有符合条件的行，并在单独的num_people列中显示5。

为什么使用COUNT(*)会出现这种问题？有没有更好的方法同时返回总行数和指定行数据？

原因分析

COUNT(*)是聚合函数，默认会把整个查询结果集合并成一组统计总数。当SELECT语句同时包含普通列（如first、last_name）和聚合函数时，若未用GROUP BY指定分组字段，数据库会将所有符合条件的行视为一个分组，仅返回一行聚合结果——那些普通列的值其实是数据库从分组中随机选取的某一行数据，这就是你看到“仅返回一行随机数据”的核心原因。

而换成5 AS num_people时，只是给每行添加一个固定值的列，没有触发聚合逻辑，数据库会正常返回所有符合LIMIT限制的行。

解决方案

以下是几种常用的、可同时获取分页数据和符合条件总行数的方法：

方法1：子查询统计总行数

把统计逻辑放到子查询中，作为单独列返回：

SELECT 
    first,
    last_name,
    date_joined,
    age,
    (SELECT COUNT(*) FROM foo_table WHERE city = 'bar') AS num_people
FROM
    foo_table
WHERE
    city = 'bar'
ORDER BY date_joined DESC
LIMIT 0, 50;

优点是逻辑简单直观；缺点是数据库会执行两次相同的WHERE条件查询，数据量大时性能会受影响。

方法2：使用`SQL_CALC_FOUND_ROWS`（仅MySQL支持）

MySQL提供的特殊关键字，执行分页查询时会自动统计符合条件的总行数，后续用SELECT FOUND_ROWS()获取总数：

SELECT SQL_CALC_FOUND_ROWS
    first,
    last_name,
    date_joined,
    age
FROM
    foo_table
WHERE
    city = 'bar'
ORDER BY date_joined DESC
LIMIT 0, 50;

-- 执行完上面的查询后，执行这条获取总行数
SELECT FOUND_ROWS() AS num_people;

优点是仅扫描一次数据，性能优于方法1；但注意该特性在MySQL 8.0.17后被标记为废弃，未来版本可能移除。

方法3：窗口函数（主流数据库通用）

使用COUNT(*) OVER()窗口函数，它会在每行数据中返回符合条件的总行数，且不会触发聚合：

SELECT 
    first,
    last_name,
    date_joined,
    age,
    COUNT(*) OVER() AS num_people
FROM
    foo_table
WHERE
    city = 'bar'
ORDER BY date_joined DESC
LIMIT 0, 50;

这是最推荐的现代解决方案，无需执行两次查询，兼容MySQL 8+、PostgreSQL、SQL Server等支持SQL:2003标准的数据库，逻辑清晰且性能稳定。

内容的提问来源于stack exchange，提问作者LeCarbonator

如何在单查询中返回指定列与总行数并实现分页？

问题描述 #

原因分析 #

解决方案 #