力扣sql入门篇(十)

力扣sql入门篇(十)

1 查找重复的电子邮箱

1.1 题目内容

1.1.1 基本题目信息

1.1.2 示例输入输出

1.2 示例sql语句

SELECT Email
FROM Person
GROUP BY Email
HAVING count(id)>=2

1.3 运行截图

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

2.1 题目内容

2.1.1 基本题目信息

2.1.2 示例输入输出

2.2 示例sql语句

SELECT actor_id,director_id
FROM ActorDirector
GROUP BY actor_id,director_id
HAVING count(*)>=3

2.3 运行截图

3 银行账户概要II

3.1 题目内容

3.1.1 基本题目信息

3.1.2 示例输入输出

3.2 示例sql语句

SELECT u.name,t.balance
FROM Users u
LEFT JOIN 
(
  SELECT account,sum(amount) balance
  FROM Transactions
  GROUP BY account
  HAVING sum(amount)>10000 
) t
ON u.account=t.account
WHERE t.balance is not null

3.3 运行截图

4 销售分析III

4.1 题目内容

4.1.1 基本题目信息

4.1.2 示例输入输出

4.2 示例sql语句

# 因为会有重复数据,所以需要去重
SELECT distinct p.product_id,p.product_name
FROM Product p
INNER JOIN Sales s
ON p.product_id=s.product_id
WHERE p.product_id NOT IN
(
    SELECT distinct(product_id)
    FROM Sales
    WHERE sale_date NOT BETWEEN  "2019-01-01" AND "2019-03-31"
);

4.3 运行截图