在每一行上添加count（）列

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.

我有一个复杂的查询与很多联合会搜索多个表的特定关键字（用户输入）。搜索到的所有表都与表簿相关。

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!

谢谢！

6 个解决方案

#1

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_ROWS option 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

#2

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列将包含每个组中的记录数，即每个标题的记录数。

#3

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

#4

My Solution:

我的解决方案

SELECT COUNT(1) over(partition BY text) totalRecordNumber
  FROM (SELECT 'a' text, id_consult_req
          FROM consult_req cr);

#5

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

如果您的问题只是执行第二个（复杂）查询的速度/成本，我建议您只需将结果集选择到哈希表中，然后在返回时从那里计算行数，或者甚至更有效地使用前一个的行数结果集，那么你甚至不必重新计算

#6

-4

You simply cannot do this, you'll have to use a second query.

您根本无法做到这一点，您将不得不使用第二个查询。

#1