Leetcode_10 天 SQL 入门_day10

这个也要结束了啊。

182. Duplicate Emails

Analysis

题意比较简单，找出重复的电子邮件即可。

Code

先统计邮件地址的出现次数：

select distinct email, count(email) as cnt
from Person
group by email

在找出出现次数大于 2 的邮件地址：

select email as email
from (
    select distinct email, count(email) as cnt
    from Person
    group by email
) as t
where t.cnt > 1

因为 WHERE 无法与聚合函数一起用，但 HAVING 可以，那就存在另外一种做法了：

select email as Email
from Person
group by Email
having count(email) > 1

1050. Actors and Directors Who Cooperated At Least Three Times

Analysis

找出演员与导演合作次数大于 3 次的一对演员 id 与导演 id。

Code

思路跟上个题一样：

select actor_id, director_id
from (
    select actor_id, director_id, count(director_id) as cnt
    from ActorDirector as ad
    group by actor_id, director_id
) as t
where t.cnt > 2

select actor_id, director_id
from ActorDirector as ad
group by actor_id, director_id
having count(director_id) > 2

1587. Bank Account Summary II

Analysis

找出余额大于 10000 的人。

Code

先统计账户的总共余额：

select account, sum(amount) as balance
from Transactions
group by account

接着用左连接查询余额大于 10000 的用户与对应余额：

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

此时得到的结果是所有人的名字，余额少于 10000 的余额栏则会填入 null，实际上这是连接导致的笛卡尔积，所以得再次查询一下：

select name as NAME, balance as BALANCE
from (
    select name, balance
    from Users as u
    left join (
        select account, sum(amount) as balance
        from Transactions
        group by account
    ) as t
    on t.balance > 10000 and t.account = u.account
) as a
where balance is not null

实际上可以写的更简单一点：

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

1084. Sales Analysis III

Analysis

跟前面几个题差不多，但是麻烦的地方在于需要判断一下“仅”。

Code

为了判断唯一性，可以用 MAX 和 MIN 函数来完成：

select product_id, product_name
from Sales as s
join Product as p
on s.product_id = p.product_id
group by s.product_id
having min(sale_date) >= '2019-01-01' and max(sale_date) <= '2019-03-31'

Summary

话说，感觉今天做的几个题都比较简单，可能是已经做了一些题，熟悉了的缘故。
话说，真快啊，这个也做完了，不过，这些东西感觉好像很容易忘记，好像已经有点记不清前几天做的什么题了😂。
总之，还是要多复习复习。

---