day09 SQL入门-过滤

182. 查找重复的电子邮箱
编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。

方法一：
select
    p.Email
from 
    Person p 
group by 
    p.Email
having
    count(p.Email)>1
方法二：
select
    distinct p1.Email as Email 
from 
    Person p1, Person p2
where 
    p1.Email=p2.Email and p1.Id>p2.Id

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

写一条SQL查询语句获取合作过至少三次的演员和导演的 id 对 (actor_id, director_id)

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

1587. 银行账户概要 II

写一个 SQL, 报告余额高于 10000 的所有用户的名字和余额. 账户的余额等于包含该账户的所有交易的总和.

返回结果表单没有顺序要求.

查询结果格式如下例所示.

方法一：
select 
    u.name,
    p.amount as balance 

from 
    Users as u,
		(
        select
            t.account,
			sum(t.amount) as amount
        from
            Transactions as t 
        group by 
            t.account
        having 
            sum(t.amount)>10000		
		) as p
where
	u.account=p.account
方法二：
select 
    u.name,
    sum(t.amount) as balance 
from 
    Users as u,
    Transactions as t 
where
    u.account=t.account
group by 
    t.account
having 
    sum(t.amount)>10000	

1084. 销售分析III

编写一个SQL查询，报告2019年春季才售出的产品。即仅在2019-01-01至2019-03-31（含）之间出售的商品。

以 任意顺序 返回结果表。

查询结果格式如下所示。

方法一：
select
	p.product_id,
	p.product_name
from 
	product as p 
	left join 
	sales as s
	on p.product_id=s.product_id
group by 
	p.product_id
having
    min(s.sale_date)>='2019-01-01' and max(s.sale_date)<='2019-03-31'
方法二：
SELECT
	p.product_id,
	product_name 
FROM
	sales s,
	product p 
WHERE
	s.product_id = p.product_id 
GROUP BY
	p.product_id 
HAVING
	SUM( sale_date < '2019-01-01' )= 0 
	AND SUM( sale_date > '2019-03-31' )= 0;