【面试题】

评分表里记录了各个城市房源的评分，包括房源号、城市、分数。

为了帮助用户根据评分找到优质房源，现在领导要你找出分数（满分10分）在0-5分，5-7分，7-9分，9分及以上分别有多少。

【解题思路】

1.拆解问题

使用逻辑树分析方法来拆解问题：求分数在0-5分，5-7分，7-9分，9分及以上分别有多少

1）将评分表按照分数进行分组，按照0-5分，5-7分，7-9分，9分四个区间进行分组

2）再对每个区间的房源进行计数

2.如何按条件拆分成多个组呢？

根据分数所在的区间进行分组，以下是一个简单的示意图

这里会有两个常见的误区：

1）分组就想到用group by，但是在这里是不行的。

因为平时使用groupby分组的字段是我们直接用的字段，而这里的分数并不是我们最终分组的区间字段0-5、5-7这样。

如果直接用分组（group by），就会把相同的分数分为一组，与我们要求的分数段是不相符的。

2）看到条件就想到用where，但是在这里是多条件的情况。如果我们使用where分数<5 那么分数大于5的房源就无法统计到

3.如何实现多条件判断呢？

涉及到“多条件判断”的问题，要想到《猴子从零学会SQL》里讲过的条件判断语句case when，完整的用法如下：

case when 字段1 > 值1 then 新值1      when 字段1 < 值1 then 新值2（可省略）      else 新值3 （可省略）end as 别名

翻译过来就是当字段1 >值1，取新值1 ；当字段1 <值1，取新值2；当字段1 =值1，取新值3

方法一：

使用四个case when就把原表分成了4个部分，省略了else，当符合条件就取房源号放入对应的字段中

(case when 分数<5 then 房源号 end) as '0-5',(case when 分数>=5 and 分数<7 then 房源号 end) as '5-7',(case when 分数>=7 and 分数<9 then 房源号 end) as '7-9',(case when 分数>=9 then 房源号 end) as '9+'

在上面代码的基础上再加上计数函数 count即可，完整SQL如下：

select count(case when 分数<5 then 房源号 end) as '0-5',       count(case when 分数>=5 and 分数<7 then 房源号 end) as '5-7',       count(case when 分数>=7 and 分数<9 then 房源号 end) as '7-9',       count(case when 分数>=9 then 房源号 end) as '9+'from 评分表;

查询结果：

方法二：

当然有同学会问，为什么不像示例一样用1个case when，而用4个case when呢？

如果用一个case when是这样的：

case when 分数<5 then 房源号          when 分数>=5 and 分数<7 then 房源号          when 分数>=7 and 分数<9 then 房源号          else 分数>=9 then 房源号 end

这样取出来的是一个字段，我们就没法区分各个组的个数。

当然如果要使用这种方法的话我们可以在then后面做点文章，直接将分数转化为分数区间。

select 房源号,城市,分数,      (case when 分数<5 then '0-5'            when分数>=5 and 分数<7 then '5-7'            when分数>=7 and 分数<9 then '7-9'            else'9+' end) as 区间from 评分表;

这样就相当于把分数用分数段来进行表示，实际结果如下图

针对这个查询结果在外层加一个查询根据区间group by即可。

select a.区间,count(房源号) 个数 from (select 房源号,城市,分数,case when 分数<5 then '0-5'      when分数>=5 and 分数<7 then '5-7'      when分数>=7 and 分数<9 then '7-9'      else'9+' end as 区间 from 评分表) as agroup by a.区间order by 区间;

查询结果：

【本题考点】

多条件判断分组类型的问题，要想到用多条件判断case解决

【举一反三】

下图是学生考试的成绩表，求各科不及格（<60）、及格（60-80）、优秀（80+）的人数

参考答案

select 课程,      count(case when 成绩<60 then 学号 end) as '不及格',      count(case when 成绩>=60 and 成绩<80 then 学号end) as '及格',      count(case when 成绩>=80 then 学号 end) as '优秀'from 成绩表group by 课程;

查询结果：

ITPub博客

如何找到优质房源？

最新文章