为什么COUNT()仅显示表格的一行? [英] Why does COUNT() show only one row of table?

问题描述

我在数据库menagerie中具有下表pet:

I have the following table pet in the database menagerie:

+--------+-------------+---------+------+------------+------------+
| name   | owner       | species | sex  | birth      | death      |
+--------+-------------+---------+------+------------+------------+
| Tommy  | Salman Khan | Lebre   | NULL | 1999-01-13 | 0000-00-00 |
| Bowser | Diane       | dog     | m    | 1981-08-31 | 1995-07-29 |
+--------+-------------+---------+------+------------+------------+

现在，如果我运行以下查询:

Now If I run the following query:

select owner, curdate() from pet;  

我得到以下输出:

+-------------+------------+
| owner       | curdate()  |
+-------------+------------+
| Salman Khan | 2016-09-12 |
| Diane       | 2016-09-12 |
+-------------+------------+

输出显示owner的所有值，以及每一行中curdate()返回的值.

The output show all the values of owner, and the value returned from curdate() in each row.

现在，如果我运行以下查询:

Now if I run the following query:

select owner, count(*) from pet;  

我得到以下输出:

+-------------+----------+
| owner       | count(*) |
+-------------+----------+
| Salman Khan |        2 |
+-------------+----------+  

我的问题是curdate()和count()函数之间的区别是什么，它使MySQL在第一个示例中输出第二个owner Diane ?

My question is what is the difference between curdate() and count() function which makes MySQL to output the second owner Diane in the first example?

推荐答案

COUNT()是通常与GROUP BY子句组合的聚合函数.

COUNT() is an aggregation function which is usually combined with a GROUP BY clause.

curdate()是日期函数，用于输出当前日期.

curdate() is a date function which outputs the current date.

仅MySQL(据我所知)允许使用这种语法，而无需使用GROUP BY子句.由于您未提供它，因此COUNT(*)将对表中的行总数进行计数，并且owner列将被随机选择/优化器默认值/根据索引进行选择.

Only MySQL (as far as I know of) allows this syntax without using the GROUP BY clause. Since you didn't provide it, COUNT(*) will count the total amount of rows in the table , and the owner column will be selected randomly/optimizer default/by indexes .

这应该是您的查询:

select owner, count(*) 
from pet
group by owner;

哪个告诉优化器为每个所有者计算总行数.

Which tells the optimizer to count total rows, for each owner.

当未提及group by子句时-聚合函数将应用于表的整个数据.

When no group by clause mentioned - the aggregation functions are applied on the entire data of the table.

通常无法使用COUNT()来完成将应用于每行的计数，通常无法与分析功能-> COUNT() OVER(PARTITION...)一起使用，但是不幸的是， MySQL的.您的另一个选择是为该附加列创建一个JOIN/CORRELATED QUERY.

A count that will be applied on each row can't be normally done with COUNT() and usually used with an analytic function -> COUNT() OVER(PARTITION...) which unfortunately doesn't exist in MySQL. Your other option is to make a JOIN/CORRELATED QUERY for this additional column.

另一个:如果要在每个所有者旁边统计总数，则可以使用子查询:

Another If you want to total count next to each owner, you can use a sub query:

SELECT owner,
       (SELECT COUNT(*) FROM pet) as cnt
FROM pet

这篇关于为什么COUNT()仅显示表格的一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！