mysql 统计 group

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