面试笔试常考的MySQL数据库操作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

添加一些测试数据:

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

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)

相关推荐