关于一个sql问题的思考

     我在面试的的时候一般都会问下候选人对sql的掌握情况，其中有一个这样的题目，也是很常见的。

 

     表结构如下：

     
 

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `name` varchar(255) DEFAULT NULL,
  `category` varchar(255) DEFAULT NULL,
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('zhangsan', 'shuxue', '85');
INSERT INTO `student` VALUES ('zhangsan', 'yuwen', '85');
INSERT INTO `student` VALUES ('zhangsan', 'yingyu', '85');
INSERT INTO `student` VALUES ('lisi', 'shuxue', '76');
INSERT INTO `student` VALUES ('lisi', 'yuwen', '85');
INSERT INTO `student` VALUES ('wangwu', 'shuxue', '85');

   

    要求是返回所有成绩都大于80分的学生名单。

    总体来看，完全答对的不多。

    首先来看下题目，这个里面学生的成绩不是固定的，所以就不能采用固定3科的方式，很多人想这么筛选。下面我就来介绍下这个要求的两种写法

1 分组过滤

select name from student group by name having min(score) >= 80;

 

     分组过滤是所有的人都想到了，基本上上来都能按照那么进行分组，但是很多人写的时候最大的问题是不知道score能不能出现在having语句里面。 这个就令我想到了，当时学习sql的时候，经常说有一些限制，什么group语句里面出现的，可以出现在select里面等等，所以估计是候选人这个地方有点懵了，不知道这个sql到底对不对。

      其实很好理解，group 语句会产生一个临时表，只是相同name名称的student是一组，那对组内的数据继续过滤肯定是没有问题的。

     那下面的sql可不可以呢？

select name,score from student group by name having min(score) >= 80;   

    

       这个sql可以执行，但是score会彻底的乱掉，因为从原来上来说分组后有三条记录，这个地方要score，该给那一条记录的score呢，只能随便返回一个记录的score。

 

2 采用in语句

select distinct name from student where name not in (select distinct name from student where score < 80);

 

     这个的思路很清晰，将分数小于80分的人员名单拿到，然后再过滤掉这些人。

     这个里面可以看到有两个distinct，这个也是候选人写到这个sql的时候，我会继续问下distinct的含义，不加可以不？我们知道，如果外层不加distinct是决定不行的，会产生重复记录，为什么会产生重复记录呢？

      这个也得从原来上来说，in语句里层的sql会产生一个临时表，然后外层的sql会去扫描，外层的原表里面出现三个zhangsan，它都不出现在里层的结果集里面，所以当然返回三条记录。那里层sql里面的distinct到底可不可以不加呢，答案是可以不加，但是这个地方要加，in语句里层返回的结果集要尽可能的少，这样比较的次数会比较少。

    从这个小题目来看，很多人在面试的时候可能临时看了sql优化的一些技巧，大谈profile、加索引呀、分表分库等，但是sql功底很差，所以大家还是要注重基础，多思考。

评论

1 楼 jinianjun 2014-11-15  

select name,score from student group by name having min(score) >= 80;   
这个语句确定是可以执行吗？ sql server中好像是不可以吧。