欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  数据库

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

程序员文章站 2024-02-06 18:19:52
...

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

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


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


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

[sql] view plaincopyprint?面试笔试常考的mysql 数据库操作group by .面试笔试常考的mysql 数据库操作group by .

  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?面试笔试常考的mysql 数据库操作group by .面试笔试常考的mysql 数据库操作group by .

  1. mysql> select * from student where id
  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




给出需求，写出sql：


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


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





所以sql语句为：







[sql] 
view plaincopyprint?面试笔试常考的mysql 数据库操作group by .面试笔试常考的mysql 数据库操作group by .



    

  1. mysql> select *, max(score) as max  from student group by dept order by name;  
    2. 

  2. 
+----+------+------+-------+---------+------+
  

    3. 

  3. 
| id | name | sex  | score | dept    | max  |  

    4. 

  4. 
+----+------+------+-------+---------+------+
  

    5. 

  5. |  1 | a    |    1 |    90 | dev     |   90 |  
    6. 

  6. |  3 | b    |    0 |    88 | design  |   88 |  
    7. 

  7. |  4 | c    |    0 |    60 | sales   |   89 |  
    8. 

  8. |  6 | d    |    1 |   100 | product |  100 |  
    9. 

  9. 
+----+------+------+-------+---------+------+
  

    10. 

  10. 
4 rows in set (0.00 sec)  

    11. 

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?面试笔试常考的mysql 数据库操作group by .面试笔试常考的mysql 数据库操作group by .



    

  1. mysql> select *,max(score) as max from student group by dept having sex='1' order by name;  
    2. 

  2. 
+----+------+------+-------+---------+------+
  

    3. 

  3. 
| id | name | sex  | score | dept    | max  |  

    4. 

  4. 
+----+------+------+-------+---------+------+
  

    5. 

  5. |  1 | a    |    1 |    90 | dev     |   90 |  
    6. 

  6. |  6 | d    |    1 |   100 | product |  100 |  
    7. 

  7. 
+----+------+------+-------+---------+------+
  

    8. 

  8. 
2 rows in set (0.46 sec)  

    9. 

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?面试笔试常考的mysql 数据库操作group by .面试笔试常考的mysql 数据库操作group by .



    

  1. mysql> select *,max(score) as max from student where sex='1' group by dept order by name;  
    2. 

  2. 
+----+------+------+-------+---------+------+
  

    3. 

  3. 
| id | name | sex  | score | dept    | max  |  

    4. 

  4. 
+----+------+------+-------+---------+------+
  

    5. 

  5. |  1 | a    |    1 |    90 | dev     |   90 |  
    6. 

  6. |  6 | d    |    1 |   100 | product |  100 |  
    7. 

  7. 
+----+------+------+-------+---------+------+
  

    8. 

  8. 
2 rows in set (0.05 sec)  

    9. 

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?面试笔试常考的mysql 数据库操作group by .面试笔试常考的mysql 数据库操作group by .



    

  1. mysql> select *,max(score) as max from student   group by dept having sum(score)>150 order by name;  
    2. 

  2. 
+----+------+------+-------+---------+------+
  

    3. 

  3. 
| id | name | sex  | score | dept    | max  |  

    4. 

  4. 
+----+------+------+-------+---------+------+
  

    5. 

  5. |  1 | a    |    1 |    90 | dev     |   90 |  
    6. 

  6. |  6 | d    |    1 |   100 | product |  100 |  
    7. 

  7. 
+----+------+------+-------+---------+------+
  

    8. 

  8. 
2 rows in set (0.00 sec)  

    9. 

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?面试笔试常考的mysql 数据库操作group by .面试笔试常考的mysql 数据库操作group by .



    

  1. mysql> select distinct dept from student;  
    2. 

  2. 
+---------+   

    3. 

  3. | dept    |  
    4. 

  4. 
+---------+   

    5. 

  5. | dev     |  
    6. 

  6. | design  |  
    7. 

  7. | sales   |  
    8. 

  8. | product |  
    9. 

  9. 
+---------+   

    10. 

  10. 
4 rows in set (0.02 sec)  

    11. 

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





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







[sql] 
view plaincopyprint?面试笔试常考的mysql 数据库操作group by .面试笔试常考的mysql 数据库操作group by .



    

  1. mysql> select name,distinct dept from student;  
    2. 

  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  

    3. 

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?面试笔试常考的mysql 数据库操作group by .面试笔试常考的mysql 数据库操作group by .



    

  1. mysql> select distinct  dept,name from student;  
    2. 

  2. 
+---------+------+   

    3. 

  3. 
| dept    | name |  

    4. 

  4. 
+---------+------+   

    5. 

  5. | dev     | a    |  
    6. 

  6. | dev     | b    |  
    7. 

  7. | design  | b    |  
    8. 

  8. | sales   | c    |  
    9. 

  9. | product | d    |  
    10. 

  10. | product | m    |  
    11. 

  11. 
+---------+------+   

    12. 

  12. 
6 rows in set (0.00 sec)  

    13. 

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?面试笔试常考的mysql 数据库操作group by .面试笔试常考的mysql 数据库操作group by .



    

  1. mysql> select  dept,name from student group by dept;  
    2. 

  2. 
+---------+------+   

    3. 

  3. 
| dept    | name |  

    4. 

  4. 
+---------+------+   

    5. 

  5. | design  | b    |  
    6. 

  6. | dev     | a    |  
    7. 

  7. | product | d    |  
    8. 

  8. | sales   | c    |  
    9. 

  9. 
+---------+------+   

    10. 

  10. 
4 rows in set (0.00 sec)  

    11. 

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去重，并带上其他列值，我们就需要尝试换个思路，可能答案自然就找到了。
相关标签： 面试 笔试 常考 mysql 数据库 操作 group 面试

上一篇： ADO.NET中的.NET数据提供者

下一篇： 解析php根据ip查询所在地区(非常有用,赶集网就用到)_PHP教程

推荐阅读