MySQL 在每一行上添加 count() 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2873129/
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
adding count( ) column on each row
提问by Nathan
I'm not sure if this is even a good question or not.
我不确定这是否是一个好问题。
I have a complex query with lot's of unions that searches multiple tables for a certain keyword (user input). All tables in which there is searched are related to the table book.
我有一个复杂的查询，其中包含许多联合，可在多个表中搜索某个关键字（用户输入）。搜索到的所有表都与该表相关book。
There is paging on the resultset using LIMIT, so there's always a maximum of 10 results that get withdrawn.
使用 LIMIT 对结果集进行分页，因此始终最多有 10 个结果被撤回。
I want an extra column in the resultset displaying the total amount of results found however. I do not want to do this using a separate query. Is it possible to add a count() column to the resultset that counts every result found?
我希望结果集中有一个额外的列显示找到的结果总数。 我不想使用单独的查询来做到这一点。是否可以将 count() 列添加到计算每个找到的结果的结果集中？
the output would look like this:
输出将如下所示：
ID Title Author Count(...)
1 book_1 auth_1 23
2 book_2 auth_2 23
4 book_4 auth_.. 23
...
...
Thanks!
谢谢！
采纳答案by Ike Walker
This won't add the count to each row, but one way to get the total count without running a second query is to run your first query using the SQL_CALC_FOUND_ROWSoption and then select FOUND_ROWS(). This is sometimes useful if you want to know how many total results there are so you can calculate the page count.
这不会将计数添加到每一行，但在不运行第二个查询的情况下获取总计数的一种方法是使用该SQL_CALC_FOUND_ROWS选项运行您的第一个查询，然后选择FOUND_ROWS()。如果您想知道总共有多少结果以便计算页数，这有时很有用。
Example:
例子：
select SQL_CALC_FOUND_ROWS ID, Title, Author
from yourtable
limit 0, 10;
SELECT FOUND_ROWS();
From the manual: http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_found-rows
从手册：http: //dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_found-rows
回答by Guffa
The usual way of counting in a query is to group on the fields that are returned:
在查询中计数的常用方法是对返回的字段进行分组：
select ID, Title, Author, count(*) as Cnt
from ...
group by ID, Title, Author
order by Title
limit 1, 10
The Cnt column will contain the number of records in each group, i.e. for each title.
Cnt 列将包含每个组（即每个标题）中的记录数。
回答by Michael Buen
Regarding second query:
关于第二个查询：
select tbl.id, tbl.title, tbl.author, x.cnt
from tbl
cross join (select count(*) as cnt from tbl) as x
If you will not join to other table(s):
如果您不加入其他表：
select tbl.id, tbl.title, tbl.author, x.cnt
from tbl, (select count(*) as cnt from tbl) as x
回答by paulo
My Solution:
我的解决方案：
SELECT COUNT(1) over(partition BY text) totalRecordNumber
FROM (SELECT 'a' text, id_consult_req
FROM consult_req cr);
回答by Benoit Labelle
This will add the total count on each row:
这将添加每行的总计数：
select count(*) over (order by (select 1)) as Cnt,*
from yourtable
回答by Cobusve
If your problem is simply the speed/cost of doing a second (complex) query I would suggest you simply select the resultset into a hash-table and then count the rows from there while returning, or even more efficiently use the rowcount of the previous resultset, then you do not even have to recount
如果您的问题只是进行第二次（复杂）查询的速度/成本，我建议您只需将结果集选择到哈希表中，然后在返回时从那里计算行数，或者甚至更有效地使用前一个的行数结果集，那么你甚至不必重新计数
回答by ceteras
You simply cannot do this, you'll have to use a second query.
您根本无法做到这一点，您必须使用第二个查询。
