我有一个正在执行的SELECT
查询,它有一个MAX()
列和一个GROUP BY
子句,除了需要返回给客户机的这个查询的结果之外,我还需要返回所有结果的总数。
基本上我的问题是这样的:
SELECT unique_id, col1, col2, MAX(col3) as col3
FROM tbl
GROUP BY col1, col2
它通常也会有一个WHERE
子句。
unique_id
是表的主键。
在将此数据返回给客户机时,我还指定了LIMIT
和OFFSET
子句来限制一次检索结果的数量。我的问题是,如果上面的查询没有LIMIT
和OFFSET
子句,我还需要显示它将产生的结果的总数,这样客户机就可以稍后/增量地检索其余的结果。
我知道我可以很容易地使用WITH
临时表来获得我想要的东西:
WITH temp AS (
SELECT unique_id, col1, col2, MAX(col3) as col3
FROM tbl
GROUP BY col1, col2
)
SELECT count(*) FROM temp
但我担心这样做的效率。sans-LIMIT
-and-OFFSET
查询可能返回数以万计的行,因此我认为使用WITH
方法获取总计数不是最好的方法。
有没有更有效的方法,我没有想到?或者,WITH
方法很好(例如,MySQL服务器是否足够“智能”,不会分配整个查询结果集来获取计数)?
示例数据
假设这是我的表中的数据:
unique_id col1 col2 col3
___________________________
1 5 8 30
2 5 8 33
3 5 9 40
4 6 8 30
5 6 8 31
6 6 8 32
7 6 9 39
8 7 8 33
9 7 8 32
10 8 8 34
因此,我的SELECT
查询将返回以下内容(假设客户端指定了LIMIT 4 OFFSET 0
):
SELECT unique_id, col1, col2, max(col3) as col3
FROM tbl
GROUP BY col1, col2
LIMIT 4
OFFSET 0;
unique_id col1 col2 col3
___________________________
2 5 8 33
3 5 9 40
6 6 8 32
7 6 9 39
然后,我使用不带LIMIT
和OFFSET
子句的查询作为子查询,并使用其中的SELECT COUNT(*)
,这将返回6
,然后将6
和结果都返回给客户机。
发布于 2018-09-14 21:33:00
MySQL 8引入了对的支持,包括窗口聚合函数。窗口聚合函数允许您在返回非聚合数据的同时返回聚合结果。基本上,您可以通过将OVER
子句附加到常规聚合函数来将其转换为窗口聚合函数,但通常还需要指定其他选项,链接的手册中对此进行了详细说明。
您也可以在GROUP BY
查询中使用窗口聚合函数。在这些情况下,窗口聚合函数将在分组完成后应用于行集。另请注意,添加LIMIT
不会影响窗口聚合函数的结果。
综合以上几点,您可以对原始查询进行如下修改:
SELECT
unique_id,
col1,
col2,
MAX(col3) as col3,
COUNT(*) OVER () AS TotalRows
FROM
tbl
GROUP BY
col1,
col2
LIMIT
4 OFFSET 0
;
并在一次中获得原始详细数据和行数。OVER
子句没有附加子句,这意味着它适用于整个行集。
正如我已经说过的,如果LIMIT
子句附加到查询,窗口聚合函数将忽略该子句。因此,上面的TotalRows
列将反映行数,就好像没有应用限制一样。
https://stackoverflow.com/questions/52278339
复制相似问题