计算MySQL中的行以及实际行内容 [英] Count rows in MySQL along with the actual row contents

问题描述

在MySQL中有一种方法来执行单个SQL语句，返回所选行以及结果行的计数。

Is there a way in MySQL to do a single SQL statement that returns the selected rows along with the count of the result rows?

我可以这样做： / p>

I can do this:

SELECT COUNT(*) FROM BigTable WHERE firstname LIKE 'a%';

这给了我一个结果行的计数（37,781）。我可以得到这样的实际行数据：

Which gives me a single result row with the count (37,781). I can get the actual row data like this:

SELECT firstname FROM BigTable WHERE firstname LIKE 'a%';

它显示实际的37,781行。但是当我尝试合并它们，像这样：

which displays the actual 37,781 rows. But when I try to combine them, like this:

SELECT firstname, COUNT(*) FROM BigTable WHERE firstname LIKE 'a%';

我得到一行与第一行匹配的查询，匹配查询。

I get a single row with the first row that matches the query, and the total count of records that matches the query.

我想看到的是两列，37,781行。第一列应包含每行的名称，第二列应包含每行的数字'37，781'。有没有办法编写查询来完成这个？

What I'd like to see is two columns with 37,781 rows. The first column should contain the first name for each row and the second column should contain the number '37,781' for every row. Is there a way to write the query to accomplish this?

推荐答案

可以使用CROSS JOIN。子查询将获取所有 firstnames 的计数，然后它将在每一行中包含此值：

You can use a CROSS JOIN. The subquery will get the count for all firstnames and then it will include this value in each row:

SELECT firstname, d.total
FROM BigTable
CROSS JOIN 
(
   SELECT COUNT(*) total
   FROM BigTable
   WHERE firstname LIKE 'a%'
) d
WHERE firstname LIKE 'a%';

查看 SQL Fiddle with Demo

这篇关于计算MySQL中的行以及实际行内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！