php聚合函数简书,聚合函数

1 作用：将表中的一列数据作为一个整体，进行纵向计算。每个聚合函数的计算结果都是单行单列。

2 常见的聚合函数：

count:个数统计，统计一列中有多少个单元格

max:一列中的最大值

min:一列中的最小值

avg:一列的平均值

sum：一列数据的总和

3 聚合函数的计算是排除null值的,例子中没有对age的2个null进行统计

mysql> select * from employee order by salary,age,in_dpt;

+----+------+------+--------+--------+--------+

| id | name | age | salary | phone | in_dpt |

+----+------+------+--------+--------+--------+

| 2 | Jack | 24 | 2500 | 120120 | dpt2 |

| 1 | Tom | 26 | 2500 | 119119 | dpt4 |

| 3 | Rose | 22 | 2800 | 114114 | dpt3 |

| 5 | Mary | 21 | 3000 | 100101 | dpt2 |

| 6 | Alex | 26 | 3000 | 123456 | dpt1 |

| 4 | Jim | 35 | 3000 | 100861 | dpt1 |

| 12 | Tony | NULL | 3400 | 102938 | dpt3 |

| 10 | Mike | 23 | 3400 | 110110 | dpt4 |

| 8 | Rick | 24 | 3500 | 987654 | dpt3 |

| 7 | Ken | 27 | 3500 | 654321 | dpt1 |

| 11 | Jobs | NULL | 3600 | 19283 | dpt2 |

| 9 | Joe | 31 | 3600 | 110129 | dpt2 |

+----+------+------+--------+--------+--------+

12 rows in set (0.00 sec)

mysql>

mysql> select count(age) from employee;

+------------+

| count(age) |

+------------+

| 10 |

+------------+

1 row in set (0.00 sec)

mysql> select count(id) from employee;

+-----------+

| count(id) |

+-----------+

| 12 |

+-----------+

1 row in set (0.00 sec)

mysql>

3.1 解决办法

使用非空的键：主键(empoyee表中的id),count(*)---星号表示一行中不全是null就可以算作一个

使用IFNULL：count(IFNULL(age,0))

mysql> select count(*) from employee;

+----------+

| count(*) |

+----------+

| 12 |

+----------+

1 row in set (0.00 sec)

mysql> select count(ifnull(age,0)) from employee;

+----------------------+

| count(ifnull(age,0)) |

+----------------------+

| 12 |

+----------------------+

1 row in set (0.00 sec)

4 例子

mysql> select max(age)from employee;

+----------+

| max(age) |

+----------+

| 35 |

+----------+

1 row in set (0.00 sec)

mysql> select min(age)from employee;

+----------+

| min(age) |

+----------+

| 21 |

+----------+

1 row in set (0.00 sec)

mysql> select sum(age)from employee;

+----------+

| sum(age) |

+----------+

| 259 |

+----------+

1 row in set (0.00 sec)

mysql> select avg(age)from employee;

+----------+

| avg(age) |

+----------+

| 25.9000 |

+----------+

1 row in set (0.00 sec)