题47:
根据下面三个表写一段 SQL, 找到所有该公司可以投资的国家。(一家电信公司想要投资新的国家是: 该国的平均通话时长要严格地大于全球平均通话时长)
其中:
- Person表:id 是主键,每一行包含一个人的名字和电话号码,(电话号码的格式是:‘xxx-yyyyyyy’, 其中xxx是国家码(3个字符), yyyyyyy是电话号码(7个字符), x和y都表示数字. 同时, 国家码和电话号码都可以包含前导0)
- Country表:country_code是主键,每一行包含国家名和国家码,country_code的格式是’xxx’, x是数字。
- Calls表:该表无主键, 可能包含重复行每一行包含呼叫方id, 被呼叫方id和以分钟为单位的通话时长. caller_id != callee_id。
解题思路:
(1)涉及到多表时,利用限制条件从笛卡尔集中筛选进行连接。
(2)筛选条件是呼叫方或被呼叫方的id等于Person表的id,用left()函数提出Person表中电话前三位数和Country表country_code的是否相等。
(3)用having()筛选出哪个国的平均通话时长要严格地大于全球平均通话时长即可。
select b.name as country
from Person a,Country b,Calls c
where (
c.caller_id = a.id
or c.callee_id = a.id
) and LEFT(a.phone_number,3) = b.country_code
group by b.country_code
having avg(duration) > (select avg(duration) from Calls);