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

IT 面试中，数据库的相关问题基本上属于必考问题，而其中关于sql语句也是经常考察的一个重要知识点。 下面介绍下sql语句中一个比较重要的操作group by，他的重要行一方面体现在他的理解困难度，一方面体现应用中的长见性。 首先，给出一个studnet学生表： [s

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)

[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)

[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去重，并带上其他列值，我们就需要尝试换个思路，可能答案自然就找到了。

声明：本网页内容旨在传播知识，若有侵权等问题请及时与本网联系，我们将在第一时间删除处理。TEL:177 7030 7066 E-MAIL:11247931@qq.com