面试笔试常考的mysql 数据库操作group by .

IT 面试中，数据库的相关问题基本上属于必考问题，而其中关于sql语句也是经常考察的一个重要知识点。

下面介绍下sql语句中一个比较重要的操作group by，他的重要行一方面体现在他的理解困难度，一方面体现应用中的长见性。

首先，给出一个studnet学生表：

[sql] view plaincopyprint?

1. CREATE TABLE `student` (  
2.   `id` int(11) NOT NULL AUTO_INCREMENT,  
3.   `name` varchar(30) DEFAULT NULL,  
4.   `sex` tinyint(1) DEFAULT '0',  
5.   `score` int(10) NOT NULL,  
6.   `dept` varchar(10) DEFAULT NULL,  
7.   PRIMARY KEY (`id`)  
8. ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8   

CREATE TABLE `student` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(30) DEFAULT NULL,  `sex` tinyint(1) DEFAULT '0',  `score` int(10) NOT NULL,  `dept` varchar(10) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 

添加一些测试数据：

[sql] view plaincopyprint?

1. mysql> select * from student where id<10;  
2. +----+------+------+-------+---------+   
3. | id | name | sex  | score | dept    |  
4. +----+------+------+-------+---------+   
5. |  1 | a    |    1 |    90 | dev     |  
6. |  2 | b    |    1 |    90 | dev     |  
7. |  3 | b    |    0 |    88 | design  |  
8. |  4 | c    |    0 |    60 | sales   |  
9. |  5 | c    |    0 |    89 | sales   |  
10. |  6 | d    |    1 |   100 | product |  
11. +----+------+------+-------+---------+  

mysql> select * from student where id<10;+----+------+------+-------+---------+| id | name | sex  | score | dept    |+----+------+------+-------+---------+|  1 | a    |    1 |    90 | dev     ||  2 | b    |    1 |    90 | dev     ||  3 | b    |    0 |    88 | design  ||  4 | c    |    0 |    60 | sales   ||  5 | c    |    0 |    89 | sales   ||  6 | d    |    1 |   100 | product |+----+------+------+-------+---------+

给出需求，写出sql：

给出各个部门最高学生的分数。

要想得到各个部门学生，首先就要分组，按照部门把他们分组，然后在各个部门中找到分数最高的就可以了。

所以sql语句为：

[sql] view plaincopyprint?

1. mysql> select *, max(score) as max  from student group by dept order by name;  
2. +----+------+------+-------+---------+------+  
3. | id | name | sex  | score | dept    | max  |  
4. +----+------+------+-------+---------+------+  
5. |  1 | a    |    1 |    90 | dev     |   90 |  
6. |  3 | b    |    0 |    88 | design  |   88 |  
7. |  4 | c    |    0 |    60 | sales   |   89 |  
8. |  6 | d    |    1 |   100 | product |  100 |  
9. +----+------+------+-------+---------+------+  
10. 4 rows in set (0.00 sec)  

mysql> select *, max(score) as max  from student group by dept order by name;+----+------+------+-------+---------+------+| id | name | sex  | score | dept    | max  |+----+------+------+-------+---------+------+|  1 | a    |    1 |    90 | dev     |   90 ||  3 | b    |    0 |    88 | design  |   88 ||  4 | c    |    0 |    60 | sales   |   89 ||  6 | d    |    1 |   100 | product |  100 |+----+------+------+-------+---------+------+4 rows in set (0.00 sec)

这只是个简单的例子，我们可以再把这个例子复杂化，比如分数最高的必须是女生，即sex列值必须为1才挑选出，这时的sql语句应该为：

[sql] view plaincopyprint?

1. mysql> select *,max(score) as max from student group by dept having sex='1' order by name;  
2. +----+------+------+-------+---------+------+  
3. | id | name | sex  | score | dept    | max  |  
4. +----+------+------+-------+---------+------+  
5. |  1 | a    |    1 |    90 | dev     |   90 |  
6. |  6 | d    |    1 |   100 | product |  100 |  
7. +----+------+------+-------+---------+------+  
8. 2 rows in set (0.46 sec)  

mysql> select *,max(score) as max from student group by dept having sex='1' order by name;+----+------+------+-------+---------+------+| id | name | sex  | score | dept    | max  |+----+------+------+-------+---------+------+|  1 | a    |    1 |    90 | dev     |   90 ||  6 | d    |    1 |   100 | product |  100 |+----+------+------+-------+---------+------+2 rows in set (0.46 sec)

这里我们没有用where语句而是用了having,这里简单说明一下，因为我们的条件是在分组后进行的，其实分组前挑选出sex='1',然后再按照dept部门分组，也是可行的，这里就要看题目是怎么要求的：

[sql] view plaincopyprint?

1. mysql> select *,max(score) as max from student where sex='1' group by dept order by name;  
2. +----+------+------+-------+---------+------+  
3. | id | name | sex  | score | dept    | max  |  
4. +----+------+------+-------+---------+------+  
5. |  1 | a    |    1 |    90 | dev     |   90 |  
6. |  6 | d    |    1 |   100 | product |  100 |  
7. +----+------+------+-------+---------+------+  
8. 2 rows in set (0.05 sec)  

mysql> select *,max(score) as max from student where sex='1' group by dept order by name;+----+------+------+-------+---------+------+| id | name | sex  | score | dept    | max  |+----+------+------+-------+---------+------+|  1 | a    |    1 |    90 | dev     |   90 ||  6 | d    |    1 |   100 | product |  100 |+----+------+------+-------+---------+------+2 rows in set (0.05 sec)

查询出的结果时一致的，如果把选择条件改为必须部门所有人的分数之和大于150才能把分数最高的部门的人列出来，这里就必须使用having了，因为 having 里面可以使用聚合函数sum，并且也必须分完组我们才能得到这个组的总分数，才能比较是否该值大于150：

[sql] view plaincopyprint?

1. mysql> select *,max(score) as max from student   group by dept having sum(score)>150 order by name;  
2. +----+------+------+-------+---------+------+  
3. | id | name | sex  | score | dept    | max  |  
4. +----+------+------+-------+---------+------+  
5. |  1 | a    |    1 |    90 | dev     |   90 |  
6. |  6 | d    |    1 |   100 | product |  100 |  
7. +----+------+------+-------+---------+------+  
8. 2 rows in set (0.00 sec)  

mysql> select *,max(score) as max from student   group by dept having sum(score)>150 order by name;+----+------+------+-------+---------+------+| id | name | sex  | score | dept    | max  |+----+------+------+-------+---------+------+|  1 | a    |    1 |    90 | dev     |   90 ||  6 | d    |    1 |   100 | product |  100 |+----+------+------+-------+---------+------+2 rows in set (0.00 sec)

额外增加一个例子，比如我要选出不重复的部门，我们可以使用

[sql] view plaincopyprint?

1. mysql> select distinct dept from student;  
2. +---------+   
3. | dept    |  
4. +---------+   
5. | dev     |  
6. | design  |  
7. | sales   |  
8. | product |  
9. +---------+   
10. 4 rows in set (0.02 sec)  

mysql> select distinct dept from student;+---------+| dept    |+---------+| dev     || design  || sales   || product |+---------+4 rows in set (0.02 sec)

但是如果我们还要列出他的id等一些其他信息，我们如果这样：

[sql] view plaincopyprint?

1. mysql> select name,distinct dept from student;  
2. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct dept from student' at line 1  

mysql> select name,distinct dept from student;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct dept from student' at line 1

这是不行的，因为distinct只能放到开始位置，如果：

[sql] view plaincopyprint?

1. mysql> select distinct  dept,name from student;  
2. +---------+------+   
3. | dept    | name |  
4. +---------+------+   
5. | dev     | a    |  
6. | dev     | b    |  
7. | design  | b    |  
8. | sales   | c    |  
9. | product | d    |  
10. | product | m    |  
11. +---------+------+   
12. 6 rows in set (0.00 sec)  

mysql> select distinct  dept,name from student;+---------+------+| dept    | name |+---------+------+| dev     | a    || dev     | b    || design  | b    || sales   | c    || product | d    || product | m    |+---------+------+6 rows in set (0.00 sec)

为什么没有达到预期的效果，因为distinct 作用到了2个字段上，这时，我们就需要groub by 出场了。

[sql] view plaincopyprint?

1. mysql> select  dept,name from student group by dept;  
2. +---------+------+   
3. | dept    | name |  
4. +---------+------+   
5. | design  | b    |  
6. | dev     | a    |  
7. | product | d    |  
8. | sales   | c    |  
9. +---------+------+   
10. 4 rows in set (0.00 sec)  

mysql> select  dept,name from student group by dept;+---------+------+| dept    | name |+---------+------+| design  | b    || dev     | a    || product | d    || sales   | c    |+---------+------+4 rows in set (0.00 sec)

按照dept分组，自然就达到去重的目的了。所以有时候如果我们碰到了一个问题很难解决，比如用distinct去重，并带上其他列值，我们就需要尝试换个思路，可能答案自然就找到了。