Mysql笔试题

最近面试，遇到一个手写SQL的面试题，当时写了个大概（踏马的程序员谁写SQL在纸上写然后挪到电脑上去？这不是为难我胖虎吗？），没写正确，但后面给面试官讲了可以实现的原理，都是在网上看过的，但面试官说我的想法是错的。。。

尴尬，后面我找到那些博文和SQL仔细的对照和跑了一遍，发现还真是错的。。。

不多说，先看题。

 有以上数据，请手写SQL查询每门课程大于80分的学生姓名。

网上的SQL基本都是这样写的：

 SELECT DISTINCT `name` 
 FROM chengji 
 WHERE name NOT IN (
		SELECT DISTINCT name 
		FROM chengji 
		WHERE fenshu<80
 );

这种情况只适应于张三有一门课程分数不大于80分，因为他只考了两门，如果有一门成绩不大于80，他自然而然就不会出现在所有科目都大于80分的结果集当中。

但实际情况是，张三只考了两门课程，但两门课程的分数都在80分以上，那么上面的SQL执行后就会出现下图的结果，与预想中的只有“王五”一人不一致。

 那么真确的SQL应该是怎么样的呢?原理又是如何的？

我的原理是：先查询出所有分数大于80分的学生信息

SELECT `name` from chengji
WHERE fenshu > 80 

然后以name进行分组后，再以所有课程数量等于最大课程数量进行筛选

GROUP BY `name` 
HAVING COUNT(DISTINCT kecheng) = 3

但这样是不行的，因为我们现在是演示数据，数据量很小，我们一眼就能看出来有多少门课程，当数据量很大，且课程可能增加或减少，那么having后面的筛选值我们就不能写死，那么就得再使用一条查询，筛选出总共有多少门课程

SELECT COUNT(DISTINCT kecheng) 
FROM chengji

然后把前面的SQL进行拼接，那么最终就会得到这样的SQL：

SELECT `name` from chengji
WHERE fenshu > 80 
GROUP BY `name` 
HAVING COUNT(DISTINCT kecheng) = (SELECT COUNT(DISTINCT kecheng) FROM chengji);

进行查询后会得到：

 这样才能得到正确的所有课程分数均大于80分的学生姓名！