【面试题】
某房源平台有两张表来记录用户信息、和用户查看房源信息。
用户表(用户号、用户注册时间)。房源浏览日志表,字段有日志号,用户号,房源号,浏览日期。
分析出当日浏览房源10套以上并且注册超过一年的用户
【解题思路】
我们用逻辑树分析方法来拆解下问题:当日浏览房源10套以上并且注册超过一年的用户。这里我们可以看出用户需要满足两个条件:
1)当日浏览房源10套以上,浏览信息在浏览表中
2)注册时间超过一年,注册信息在注册表中
涉及2张及以上表的查询时,需想到《猴子 从零学会SQL》里讲到的,要用到多表联结。
使用哪种联结呢?
这里我们的条件在两边都是需要满足的,所以使用内联结(inner join),两表的联结字段是用户号,如下图所示
两表联结的SQL
select *
from 浏览表 as a
inner join 用户表 as b
on a.`用户号`=b.`用户号;
两表联结后,再来看题目要求的条件。
1.注册时间超过一年
这里对注册时间用where子句筛选。这里要用到计算日期的函数(date_sub
),语法如下:
例子:date_sub(NOW(),INTERVAL 1 year) 代表现在的日期减去一年,也就是去年的这个时候
select *
from 浏览表 as a
inner join 用户表 as b
on a.用户号=b.用户号
where b.注册时间 < date_sub(NOW(),INTERVAL 1 year);
2.当日浏览房源10套以上
这句话翻译成大白话就是:每个用户、每天浏览房源10套以上。
涉及到“每个”要想到用《猴子 从零学会SQL》里讲过的用“分组汇总”解决这类问题。
按“每个用户、每天”分组,汇总(对房源进行计数)。
select a.浏览日期,a.用户号,count(房源号)
from 浏览表 as a
inner join 用户表 as b
on a.用户号=b.用户号
where b.注册时间 < date_sub(NOW(),INTERVAL 1 year)
group by a.浏览日期,a.用户号
having count(房源号)>10;
【本题考点】
1.涉及到多个表,要想到用多表查询,包括使用哪种联结,使用哪些字段联结。要能熟练应用《猴子 从零学会SQL》里的下图
2.涉及到“每个”这类问题要想到用“分组汇总”。涉及到“每个+排名”问题,要想到用窗口函数
【举一反三】
下图是学生表、成绩表和课程表,找出哪些学生单科成绩超过80分,给出姓名、课程名称和成绩
参考答案
select a.姓名,c.名称,b.成绩
from 学生表 as a
inner join 成绩表 as b
on a.学号=b.学号
inner join 课程表 as c on c.课程号=b.课程号
where b.成绩>80;