mysql> select*from employee_tbl;
+----+--------+---------------------+--------+
| id | name | date | singin |
+----+--------+---------------------+--------+
| 1 | 小明 | 2016-04-22 15:25:33 | 1 |
| 2 | 小王 | 2016-04-20 15:25:47 | 3 |
| 3 | 小丽 | 2016-04-19 15:26:02 | 2 |
| 4 | 小王 | 2016-04-07 15:26:14 | 4 |
| 5 | 小明 | 2016-04-11 15:26:40 | 4 |
| 6 | 小明 | 2016-04-04 15:26:54 | 2 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)
1、统计count（*）employee_tbl表中每个人有多少条记录
mysql> select name,count(*) from employee_tbl group by name;
+--------+----------+
| name | count(*) |
+--------+----------+
| 小丽 | 1 |
| 小明 | 3 |
| 小王 | 2 |
+--------+----------+
3 rows in set (0.01 sec)
2、统计每个人的登录次数并以name：as singin_count设置singin 的别名
mysql> select name,sum(singin) as singin_count from employee_tbl group by name;
+--------+--------------+
| name | singin_count |
+--------+--------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
+--------+--------------+
3 rows in set (0.00 sec)
3、with rollup：可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）
mysql> select name,sum(singin) as singin_count from employee_tbl group by name with rollup;
+--------+--------------+
| name | singin_count |
+--------+--------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| NULL | 16 |
+--------+--------------+
4 rows in set (0.00 sec)
4、使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法
语法：select coalesce(a,b,c);
参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。
以下实例中如果名字为空我们使用总数代替：
mysql> select coalesce(name,"总数"),sum(singin) as singin_count from employee_tbl group by name with rollup;
+-------------------------+--------------+
| coalesce(name,"总数") | singin_count |
+-------------------------+--------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| 总数 | 16 |
+-------------------------+--------------+
4 rows in set (0.00 sec)
网友评论