MySQL 计算 groupBy 后的总记录数选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2697478/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
count total records after groupBy select
提问by Gabriel Solomon
I have a mysql select query that has a groupBy. I want to count all the records after the group by statement. Is there a way for this directly from mysql ?
我有一个 mysql 选择查询，它有一个 groupBy。我想计算 group by 语句之后的所有记录。有没有办法直接从 mysql 做到这一点？
thanks.
谢谢。
采纳答案by Frank Shearar
You can use FOUND_ROWS():
您可以使用FOUND_ROWS()：
SELECT <your_complicated_query>;
SELECT FOUND_ROWS();
It's really intended for use with LIMIT, telling you how many rows would have been returned without the LIMIT, but it seems to work just fine for queries that don't use LIMIT.
它确实打算与 LIMIT 一起使用，告诉您没有 LIMIT 将返回多少行，但它似乎对不使用 LIMIT 的查询工作得很好。
回答by CobaltBlueDW
If the only thing you need is the count after grouping, and you don't want to use 2 separate queries to find the answer. You can do it with a sub query like so...
如果您唯一需要的是分组后的计数，并且您不想使用 2 个单独的查询来查找答案。您可以使用像这样的子查询来做到这一点......
select count(*) as `count`
from (
select 0 as `doesn't matter`
from `your_table` yt
group by yt.groupfield
) sq
Note: You have to actually select something in the sub query, but what you select doesn't matter
注意：您必须在子查询中实际选择某些内容，但您选择的内容并不重要
Note: All temporary tables have to have a named alias, hence the "sq" at the end
注意：所有临时表都必须有一个命名的别名，因此最后是“sq”
回答by Karthik
see this query for examples:
有关示例，请参阅此查询：
This query is used to find the available rooms record for a hotel, just check this
此查询用于查找酒店的可用房间记录，只需检查此
SELECT a.type_id, a.type_name, a.no_of_rooms,
(SELECT SUM(booked_rooms) FROM reservation
WHERE room_type = a.type_id
AND start_date >= '2010-04-12'
AND end_date <= '2010-04-15') AS booked_rooms,
(a.no_of_rooms - (SELECT SUM(booked_rooms)
FROM reservation
WHERE room_type = a.type_id
AND start_date >= '2010-04-12'
AND end_date <= '2010-04-15')) AS freerooms
FROM room_type AS a
LEFT JOIN reservation AS b
ON a.type_id = b.room_type
GROUP BY a.type_id ORDER BY a.type_id
