【SQL】面试篇之排序和分组练习

1587 银行账户概要 II

• 1587题目

# Write your MySQL query statement below
select name, balance
from (
    select u.account, name, sum(amount) as balance
    from Users u
    left join Transactions t
    on u.account = t.account
    group by u.account
) temp
where balance > 10000

总结

• 这里看到有说可以直接使用having的，但该方法在mysql的更高版本是会出现错误的。

# having写法
select name, sum(amount) as balance
from Users u
left join Transactions t
on u.account = t.account
group by u.account
having balance > 10000

182 查找重复的电子邮箱

# 写法1
select email
from Person
group by email
having count(email)>1

# 写法2
select distinct a.email
from Person a
join Person b
on a.email=b.email and a.id != b.id;

1050 合作过至少三次的演员和导演

select actor_id, director_id
from ActorDirector
group by actor_id, director_id
having count(*) >= 3;

1511 消费者下单频率

• 1511题

select c.customer_id, c.name
from Customers c
join orders o on c.customer_id = o.customer_id
join Product p
on p.product_id = o.product_id
group by c.customer_id, c.name
having sum(case when left(o.order_date, 7) = '2020-06' then quantity*price else 0 end) >= 100
and sum(case when left(o.order_date, 7) = '2020-07' then quantity*price else 0 end) >= 100

总结

• 分组查询关键字group by通常和集合函数（MAX、MIN、COUNT、SUM、AVG）一起使用，它可以对一列或者多列结果集进行分组。一般情况下，我们在使用group by的时候，select中的列都要出现在group by中。
• 下面该例子执行错误：

select id,name,sum(salary) from t_salary group by id;

因为group by时，select涉及的列要么是分组的依据（本例：id），要么包含在聚合函数（本例：salary）中。

1501 可以放心投资的国家

• 1501题

select c2.name as country 
from Calls c1,Person p,Country c2
where (p.id=c1.caller_id or p.id=c1.callee_id) and c2.country_code=left(p.phone_number,3)
group by c2.name 
having avg(duration)>(select avg(duration) from Calls)

总结

• 题目思路，我们将通话记录calls和人员信息表连接起来，这样我们可以获得对应通话记录的人员电话号码，进而可以根据电话号码将之和国家号连接起来。最后根据国家分组，筛选出分组后计算的平均值大于总体平均值的国家。

经过练习，发现自己很多时候思路不是很清晰，之前自己都是写一半然后根据数据写出最后答案，但面试时，我们应该学会分解问题，一步步操作。