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

页面导航:首页 > 数据库 > Mysql > 面试笔试常考的mysql数据库操作groupby

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

来源: 作者: 时间:2016-01-26 09:03 【

IT 面试中,数据库的相关问题基本上属于必考问题,而其中关于sql语句也是经常考察的一个重要知识点。下面介绍下sql语句中一个比较重要的操作group by,他的重要行一方面体现在他的理

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

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

首先,给出一个studnet学生表:

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  

添加一些测试数据:

> 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语句为:

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语句应该为:

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部门分组,也是可行的,这里就要看题目是怎么要求的:
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:

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 distinct dept from student;
+---------+
| dept    |
+---------+
| dev     |
| design  |
| sales   |
| product |
+---------+
4 rows in set (0.02 sec) 

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

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只能放到开始位置,如果:

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

Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<