MySQL 在一个查询中选择计数和其他记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1872225/
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
select count and other records in one single query
提问by developer
I have the following query
我有以下查询
select main_cat_name,cat_url from mf_main order by main_cat_name
This returns whole data of my table.Now i want to have count of the total rows of this table.I can do it using another query but how can i use them in one single query???
这将返回我的表的全部数据。现在我想计算这个表的总行数。我可以使用另一个查询来完成，但我如何在一个查询中使用它们？？？
I want two data ONE :- the rows of the table TWO:- the count how can i have that in one single query
我想要两个数据 ONE :- 表的行 2:- 计数我怎么能在一个查询中得到它
I tried this but it gives correct count but displays only first row of the table :
我试过这个，但它给出了正确的计数，但只显示表的第一行：
select count(cat_id),main_cat_name,cat_url from mf_main order by main_cat_name
回答by Cristian Boariu
You can try with Group By like:
您可以尝试使用 Group By，例如：
SELECT count(cat_id), main_cat_name, cat_url FROM mf_main GROUP BY main_cat_name, cat_url ORDER BY main_cat_name
Right solution i hope:
我希望正确的解决方案：
This is what you want:)
这就是你想要的:)
SELECT x.countt, main_cat_name, cat_url FROM mf_main, (select count(*) as countt FROM mf_main) as x ORDER BY main_cat_name
If you use mysql u have "as" like i did. For others db may be without as (like oracle)
如果你使用 mysql 你像我一样有“as”。对于其他人，db 可能没有 as（如 oracle）
回答by Peter Lang
You could try
你可以试试
select main_cat_name,cat_url,
COUNT(*) OVER () AS total_count
from mf_main
order by main_cat_name
Not sure if MySQL accepts the AS, just remove it if it does not.
不确定 MySQL 是否接受AS，如果不接受就删除它。
回答by Anwar Chandra
select count(cat_id), main_cat_name, cat_url
from mf_main
group by main_cat_name, cat_url
order by main_cat_name
回答by zero8
Once you already created a select statement
一旦你已经创建了一个选择语句
there is already a num_rows record of your count as num_rows
已经有一个 num_rows 记录您的计数为 num_rows
回答by HardQuestions
If you don't use WHERE query and don't limit the output any other way, like using GROUP BY, than the rows amount in result will match the rows amount in table, so you can use database driver specific methods to find the rows count, e.g. mysql_num_rowsin PHP
如果您不使用 WHERE 查询并且不以任何其他方式限制输出，例如使用 GROUP BY，那么结果中的行数将与表中的行数匹配，因此您可以使用数据库驱动程序特定的方法来查找行计数，例如mysql_num_rows在 PHP
