IT 面试中，数据库的相关问题基本上属于必考问题，而其中关于sql语句也是经常考察的一个重要知识点。 下面介绍下sql语句中一个比较重要的操作group by，他的重要行一方面体现在他的理解困难度，一方面体现应用中的长见性。 首先，给出一个studnet学生表： [s
IT 面试中，数据库的相关问题基本上属于必考问题，而其中关于sql语句也是经常考察的一个重要知识点。
下面介绍下sql语句中一个比较重要的操作group by，他的重要行一方面体现在他的理解困难度，一方面体现应用中的长见性。
首先，给出一个studnet学生表：
[sql]
view plaincopyprint?
- 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
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
Copier après la connexion
添加一些测试数据：
[sql]
view plaincopyprint?
- mysql> select * from student where id
-
+----+------+------+-------+---------+
-
| 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 |
-
+----+------+------+-------+---------+
mysql> select * from student where id<br>
<br>
<p><span><br>
</span></p>
<p><span>给出需求，写出sql：</span></p>
<p><span>给出各个部门最高学生的分数。</span></p>
<p><span>要想得到各个部门学生，首先就要分组，按照部门把他们分组，然后在各个部门中找到分数最高的就可以了。</span></p>
<p><span><br>
</span></p>
<p><span>所以sql语句为：</span></p>
<p>
</p><p>
</p><p><strong>[sql]</strong>
view plaincopyprint?<img src="/inc/test.jsp?url=http%3A%2F%2Fblog.csdn.net%2Ffangqun663775%2Farticle%2Fdetails%2F38413197%2Fhttps%3A%2Fcode.csdn.net%2Fassets%2FCODE_ico.png&refer=http%3A%2F%2Fblog.csdn.net%2Ffangqun663775%2Farticle%2Fdetails%2F38413197" alt="面试笔试常考的mysql 数据库操作group by ." ><img src="/inc/test.jsp?url=http%3A%2F%2Fblog.csdn.net%2Ffangqun663775%2Farticle%2Fdetails%2F38413197%2Fhttps%3A%2Fcode.csdn.net%2Fassets%2Fico_fork.svg&refer=http%3A%2F%2Fblog.csdn.net%2Ffangqun663775%2Farticle%2Fdetails%2F38413197" alt="面试笔试常考的mysql 数据库操作group by ." ></p>
<ol>
<li><span><span>mysql> </span><span>select</span><span> *, </span><span>max</span><span>(score) </span><span>as</span><span> </span><span>max</span><span> </span><span>from</span><span> student </span><span>group</span><span> </span><span>by</span><span> dept </span><span>order</span><span> </span><span>by</span><span> </span><span>name</span><span>; </span></span></li>
<li>
<span>+</span><span>----+------+------+-------+---------+------+
</span><span> </span>
</li>
<li>
<span>| id | </span><span>name</span><span> | sex | score | dept | </span><span>max</span><span> | </span>
</li>
<li>
<span>+</span><span>----+------+------+-------+---------+------+
</span><span> </span>
</li>
<li><span>| 1 | a | 1 | 90 | dev | 90 | </span></li>
<li><span>| 3 | b | 0 | 88 | design | 88 | </span></li>
<li><span>| 4 | c | 0 | 60 | sales | 89 | </span></li>
<li><span>| 6 | d | 1 | 100 | product | 100 | </span></li>
<li>
<span>+</span><span>----+------+------+-------+---------+------+
</span><span> </span>
</li>
<li>
<span>4 </span><span>rows</span><span> </span><span>in</span><span> </span><span>set</span><span> (0.00 sec) </span>
</li>
</ol>
<pre class="brush:php;toolbar:false">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)
Copier après la connexion
这只是个简单的例子，我们可以再把这个例子复杂化，比如分数最高的必须是女生，即sex列值必须为1才挑选出，这时的sql语句应该为：
[sql]
view plaincopyprint?
- 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)
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)
Copier après la connexion
这里我们没有用where语句而是用了having,这里简单说明一下，因为我们的条件是在分组后进行的，其实分组前挑选出sex='1',然后再按照dept部门分组，也是可行的，这里就要看题目是怎么要求的：
[sql]
view plaincopyprint?
- 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)
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)
Copier après la connexion
查询出的结果时一致的，如果把选择条件改为必须部门所有人的分数之和大于150才能把分数最高的部门的人列出来，这里就必须使用having了，因为 having 里面可以使用聚合函数sum，并且也必须分完组我们才能得到这个组的总分数，才能比较是否该值大于150：
[sql]
view plaincopyprint?
- 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)
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)
Copier après la connexion
额外增加一个例子，比如我要选出不重复的部门，我们可以使用
[sql]
view plaincopyprint?
- mysql> select distinct dept from student;
-
+---------+
- | dept |
-
+---------+
- | dev |
- | design |
- | sales |
- | product |
-
+---------+
-
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)
Copier après la connexion
但是如果我们还要列出他的id等一些其他信息，我们如果这样：
[sql]
view plaincopyprint?
- 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
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
Copier après la connexion
这是不行的，因为distinct只能放到开始位置，如果：
[sql]
view plaincopyprint?
- 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)
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)
Copier après la connexion
为什么没有达到预期的效果，因为distinct 作用到了2个字段上，这时，我们就需要groub by 出场了。
[sql]
view plaincopyprint?
- 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)
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)
Copier après la connexion
按照dept分组，自然就达到去重的目的了。所以有时候如果我们碰到了一个问题很难解决，比如用distinct去重，并带上其他列值，我们就需要尝试换个思路，可能答案自然就找到了。
![[Web front-end] Démarrage rapide de Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
