(转载)MySQL关键字GROUP BY的使用
例子:
mysql> select * from employee; +------+------+-------+------+-------+----------+ | num | d_id | name | age | sex | homeaddr | +------+------+-------+------+-------+----------+ | 1 | 1001 | jack | 26 | man | beijing | | 2 | 1001 | mike | 25 | man | nanjing | | 3 | 1003 | alex | 24 | man | nanchang | | 4 | 1004 | rose | 14 | woman | england | | 5 | 1001 | robot | 20 | man | zhuhai | +------+------+-------+------+-------+----------+ 5 rows in set (0.00 sec) // 这里采取按照不同的年龄进行分组,当然也可以按照不同的地区分组 mysql> select * from employee group by sex; +------+------+------+------+-------+----------+ | num | d_id | name | age | sex | homeaddr | +------+------+------+------+-------+----------+ | 1 | 1001 | jack | 26 | man | beijing | | 4 | 1004 | rose | 14 | woman | england | +------+------+------+------+-------+----------+ 2 rows in set (0.00 sec) mysql> mysql> select sex, GROUP_CONCAT(name) from employee group by sex; +-------+----------------------+ | sex | GROUP_CONCAT(name) | +-------+----------------------+ | man | jack,mike,alex,robot | | woman | rose | +-------+----------------------+ 2 rows in set (0.05 sec) mysql> // 统计男、女分组中,每组的人数 mysql> select sex, count(sex) from employee group by sex; +-------+------------+ | sex | count(sex) | +-------+------------+ | man | 4 | | woman | 1 | +-------+------------+ 2 rows in set (0.00 sec) // 找出分组中个数>=的分组 mysql> select sex, count(sex) from employee group by sex HAVING COUNT(sex) >= 3; +------+------------+ | sex | count(sex) | +------+------------+ | man | 4 | +------+------------+ 1 row in set (0.00 sec) mysql> // 根据d_id分组,当相同的分组中sex不同时,在进行分组 mysql> select * from employee group by d_id, sex; +------+------+------+------+-------+----------+ | num | d_id | name | age | sex | homeaddr | +------+------+------+------+-------+----------+ | 1 | 1001 | jack | 26 | man | beijing | | 3 | 1003 | alex | 24 | man | nanchang | | 4 | 1004 | rose | 14 | woman | england | +------+------+------+------+-------+----------+ 3 rows in set (0.00 sec) mysql> select * from employee; +------+------+-------+------+-------+----------+ | num | d_id | name | age | sex | homeaddr | +------+------+-------+------+-------+----------+ | 1 | 1001 | jack | 26 | man | beijing | | 2 | 1001 | mike | 25 | man | nanjing | | 3 | 1003 | alex | 24 | man | nanchang | | 4 | 1004 | rose | 14 | woman | england | | 5 | 1001 | robot | 20 | man | zhuhai | +------+------+-------+------+-------+----------+ 5 rows in set (0.00 sec) mysql> insert into employee values(6, 1001, 'nick', 33, 'woman', 'shenzhen'); Query OK, 1 row affected (0.00 sec) mysql> select * from employee; +------+------+-------+------+-------+----------+ | num | d_id | name | age | sex | homeaddr | +------+------+-------+------+-------+----------+ | 1 | 1001 | jack | 26 | man | beijing | | 2 | 1001 | mike | 25 | man | nanjing | | 3 | 1003 | alex | 24 | man | nanchang | | 4 | 1004 | rose | 14 | woman | england | | 5 | 1001 | robot | 20 | man | zhuhai | | 6 | 1001 | nick | 33 | woman | shenzhen | +------+------+-------+------+-------+----------+ 6 rows in set (0.00 sec) // 从这里看到,当执行d_id分组时,若遇到相同的值, 则进行sex分组。 mysql> select * from employee group by d_id, sex; +------+------+------+------+-------+----------+ | num | d_id | name | age | sex | homeaddr | +------+------+------+------+-------+----------+ | 1 | 1001 | jack | 26 | man | beijing | | 6 | 1001 | nick | 33 | woman | shenzhen | | 3 | 1003 | alex | 24 | man | nanchang | | 4 | 1004 | rose | 14 | woman | england | +------+------+------+------+-------+----------+ 4 rows in set (0.00 sec) mysql> // 将所有分组的综合相加 mysql> select sex, COUNT(sex) from employee group by sex with rollup; +-------+------------+ | sex | COUNT(sex) | +-------+------------+ | man | 4 | | woman | 2 | | NULL | 6 | +-------+------------+ 3 rows in set (0.00 sec) mysql>