SQL面试题1

1.行转列2.查找成绩表中所有科目成绩均大于80的学生姓名方法一：逆向思维（not in/not exists +子查询）方法二：group by +聚合函数

1.行转列

第一次看见这个题目是在去面试的地铁上，当时看见就觉得很厉害的样子（对于小白），哪篇文章讲的是最仔细易懂的，可惜我找不到了。下面是我自己对这个题目的一次演示。 students表

NameSubjectScore小明语文92小明数学95小明英语88小强语文78小强数学88小强英语94

要利用SQL语句将其变成下表

Name语文数学英语小明929588小强788894

查询语句为：

select Name, max(case Subject when "语文" then Score else null end) as "语文", max(case Subject when "数学" then Score else null end) as "数学", max(case Subject when "英语" then Score else null end) as "英语", from students group by Name

此处必须加聚合函数max进行聚合，不然表就会变成下面这样子，出现null值。这是因为我们用了case when之后，表中没有匹配到的值就会显示null，此时用max()取最大值的那一行，即有数值的那一行。

select Name, case Subject when "语文" then Score else null end as "语文", case Subject when "数学" then Score else null end as "数学", case Subject when "英语" then Score else null end as "英语", from students group by Name Name语文数学英语小明92nullnull小明null95null小明nullnull88小强78nullnull小强null88null小强nullnull94

当然，用sum()函数效果也是一样的。 在完成了聚合之后，我们再用group by根据Name来分组。

2.查找成绩表中所有科目成绩均大于80的学生姓名

给出一个成绩表如下：Students

Namesubjectscore张三语文88张三数学77李四语文90李四英语79王五语文84王五数学91王五英语83

方法一：逆向思维（not in/not exists +子查询）

先找出存在科目成绩小于80分的学生，再去除这些学生就是所有科目成绩均大于80的学生了。

select distinct Name from Students not in(select distinct Name from Students where score<80) 或者 select distinct Name from Students not exists(select distinct Name from Students where score<80)

exists 详解：

取出外表第一条数据 ，然后与内表 根据连接条件 ， 形成一条或多条数据，判断这些生成的数据中是否存在 或者是不存在符合where条件的 。结果为ture的那条外表 记录旧被查询出来！

实例过程： 取出外表的第一条记录， 和内表通过姓名条件连接，这时候产生2两记录， 根据 not exists是判断不存在。 条件是 score<80 . 而这两条记录存在一条记录小于80，所以于not exists 不符合， 该条记录不被查出。

方法二：group by +聚合函数

select Name from Students group by min(Score)>80

这个方法效率比方法一要好，一用了distinct存在效率低下的问题。（然鹅我也不知道为什么，希望有人能告知一下原因，怎么不用distinct来实现这个） 新手求教/(ㄒoㄒ)/~~