10.过滤

182.查找重复的电子邮箱

i.

1 # Write your MySQL query statement below
2 SELECT
3   Email
4 FROM
5   Person
6 GROUP BY Email
7 HAVING COUNT(Email) > 1

ii.哈哈，这个属实是重复了

1 # Write your MySQL query statement below
2 SELECT DISTINCT
3    p1.Email
4 FROM
5    Person p1
6 WHERE
7    p1.Email IN (SELECT p2.Email FROM Person p2 GROUP BY p2.Email HAVING COUNT(p2.Email) > 1)

 

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

i.

1 # Write your MySQL query statement below
2 SELECT
3    actor_id, director_id
4 FROM
5    ActorDirector
6 GROUP BY actor_id, director_id
7 HAVING COUNT(actor_id) >= 3

ii.

1 # Write your MySQL query statement below
2 SELECT 
3     A.actor_id,A.director_id
4 FROM 
5     (SELECT a1.actor_id,a1.director_id,COUNT(a1.actor_id) num
6     FROM ActorDirector a1
7     GROUP BY a1.actor_id,a1.director_id) AS A
8 WHERE
9     A.num >= 3

 

1587.银行账户概要II

i.

1 # Write your MySQL query statement below
2 SELECT
3    Users.name NAME,SUM(Transactions.amount) BALANCE
4 FROM 
5    Users LEFT JOIN Transactions ON Users.account = Transactions.account
6 GROUP BY Users.account
7 HAVING SUM(Transactions.amount) > 10000

ii.

1 # Write your MySQL query statement below
2 SELECT
3     Users.name name, T1.cnt balance
4 FROM
5     Users LEFT JOIN (SELECT account, SUM(amount) cnt
6             FROM Transactions 
7             GROUP BY account) AS T1 ON Users.account = T1.account
8 WHERE
9     T1.cnt > 10000

 

1084.销售分析III

i.

1 # Write your MySQL query statement below
2 SELECT
3    p.product_id,p.product_name 
4 FROM
5    Product p LEFT JOIN Sales s ON p.product_id = s.product_id
6 GROUP BY p.product_id
7 HAVING MIN(sale_date) >= '2019-01-01' 
8 AND MAX(sale_date) <= '2019-03-31'

ii.

 1 # Write your MySQL query statement below
 2 SELECT
 3   product_id, 
 4   product_name
 5 FROM
 6   Product
 7 WHERE
 8   product_id in (SELECT product_id
 9                  FROM Sales
10                  GROUP BY product_id
11                  HAVING SUM(sale_date BETWEEN '2019-01-01' AND '2019-03-31') = COUNT(*))
12 
13

iii.

1 # Write your MySQL query statement below
2 SELECT p.product_id, p.product_name
3 FROM Product p, Sales s
4 WHERE p.product_id = s.product_id
5 GROUP BY s.product_id
6 HAVING sum(sale_date between '2019-01-01' and '2019.03-31') = count(*)

 

posted @ 2022-12-28 18:00  balabalahhh  阅读(21)  评论(0)    收藏  举报