1.单表聚合
表user_age 内容如下
| id | user | age |
| 1 | user1 | 11 |
| 2 | user2 | 12 |
| 3 | user1 | 12 |
| 4 | user1 | 13 |
| 5 | user3 | 14 |
| 6 | user2 | 15 |
| 7 | user3 | 16 |
现在统计每个user有多少条数据
select user,count(*) as count from user_age group by user;
结果是
| user | count |
| user1 | 3 |
| user2 | 2 |
| user3 | 2 |
2.多表聚合
表user，里面的内容为
| id | username |
| 1 | user1 |
| 2 | user2 |
| 3 | user3 |
| 4 | user4 |
| 5 | user5 |
| 6 | user6 |
现在查询每个username 在 user_age 里面存在多少个
SELECT a.*,count(a.username) as count FROM `user` a LEFT JOIN user_age b on a.username=b.user group by a.username
结果：
| id | username | count |
| 1 | user1 | 3 |
| 2 | user2 | 2 |
| 3 | user3 | 2 |
| 4 | user4 | 1 |
| 5 | user5 | 1 |
| 6 | user6 | 1 |