【LeetCode-SQL专项突破】-第10天:过滤

zz 文档中心 2022-05-23 23:34:51 38

📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️

文章目录

前言
- 182.查找重复的电子邮箱
- 1050. 合作过至少三次的演员和导演
- 1587.银行账户概要 II
- 1084. 销售分析III

前言

SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。

【LeetCode-SQL专项突破】-第10天:过滤

182.查找重复的电子邮箱

🚀 编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。示例：+----+---------+| Id | Email   |+----+---------+| 1  | a@b.com || 2  | c@d.com || 3  | a@b.com |+----+---------+根据以上输入，你的查询应返回以下结果：+---------+| Email   |+---------+| a@b.com |+---------+说明：所有电子邮箱都是小写字母。🐴🐴 答案# Write your MySQL query statement belowselect Email from Persongroup by Emailhaving count(*)>1/* Write your T-SQL query statement below */select distinct(p1.Email) Email from Person p1  join Person  p2 on p1.Email = p2.Email AND p1.Id!=p2.Id/* Write your PL/SQL query statement below */select Email "Email" from(    select Email,count(Email) as num    from Person    group by Email) where num>1

【LeetCode-SQL专项突破】-第10天:过滤

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

🚀 ActorDirector 表：+-------------+---------+| Column Name | Type    |+-------------+---------+| actor_id    | int     || director_id | int     || timestamp   | int     |+-------------+---------+timestamp 是这张表的主键.🚀 需求写一条SQL查询语句获取合作过至少三次的演员和导演的 id 对 (actor_id, director_id)示例：ActorDirector 表：+-------------+-------------+-------------+| actor_id    | director_id | timestamp   |+-------------+-------------+-------------+| 1    | 1    | 0    || 1    | 1    | 1    || 1    | 1    | 2    || 1    | 2    | 3    || 1    | 2    | 4    || 2    | 1    | 5    || 2    | 1    | 6    |+-------------+-------------+-------------+Result 表：+-------------+-------------+| actor_id    | director_id |+-------------+-------------+| 1    | 1    |+-------------+-------------+唯一的 id 对是 (1, 1)，他们恰好合作了 3 次。 🐴🐴 答案# Write your MySQL query statement belowselect actor_id,director_idfrom ActorDirectorgroup by actor_id,director_idhaving count(timestamp)>=3/* Write your T-SQL query statement below */select actor_id,director_idfrom ActorDirectorgroup by actor_id,director_idhaving count(timestamp)>=3/* Write your PL/SQL query statement below */select actor_id "actor_id",director_id "director_id"from ActorDirectorgroup by actor_id,director_idhaving count(*) > =3

【LeetCode-SQL专项突破】-第10天:过滤

1587.银行账户概要 II

🚀 表: Users+--------------+---------+| Column Name  | Type    |+--------------+---------+| account      | int     || name  | varchar |+--------------+---------+account 是该表的主键.表中的每一行包含银行里中每一个用户的账号. 🚀 需求表: Transactions+---------------+---------+| Column Name   | Type    |+---------------+---------+| trans_id      | int     || account| int     || amount | int     || transacted_on | date    |+---------------+---------+trans_id 是该表主键.该表的每一行包含了所有账户的交易改变情况.如果用户收到了钱, 那么金额是正的; 如果用户转了钱, 那么金额是负的.所有账户的起始余额为 0.写一个 SQL,  报告余额高于 10000 的所有用户的名字和余额. 账户的余额等于包含该账户的所有交易的总和.返回结果表单没有顺序要求.查询结果格式如下例所示.Users table:+------------+--------------+| account    | name  |+------------+--------------+| 900001     | Alice || 900002     | Bob   || 900003     | Charlie      |+------------+--------------+Transactions table:+------------+------------+------------+---------------+| trans_id   | account    | amount     | transacted_on |+------------+------------+------------+---------------+| 1   | 900001     | 7000|  2020-08-01   || 2   | 900001     | 7000|  2020-09-01   || 3   | 900001     | -3000      |  2020-09-02   || 4   | 900002     | 1000|  2020-09-12   || 5   | 900003     | 6000|  2020-08-07   || 6   | 900003     | 6000|  2020-09-07   || 7   | 900003     | -4000      |  2020-09-11   |+------------+------------+------------+---------------+Result table:+------------+------------+| name| balance    |+------------+------------+| Alice      | 11000      |+------------+------------+Alice 的余额为(7000 + 7000 - 3000) = 11000.Bob 的余额为1000.Charlie 的余额为(6000 + 6000 - 4000) = 8000.🐴🐴 答案# Write your MySQL query statement belowWITH t AS (SELECT account, SUM(amount) balance FROM Transactions GROUP BY account HAVING SUM(amount)>10000)SELECT name, balanceFROM t JOIN Users USING(account)/* Write your T-SQL query statement below */selectname,sum(isnull(b.amount, 0)) balance from Users aleft join Transactions bon a.account = b. accountgroup by namehaving sum(isnull(b.amount, 0))>10000/* Write your PL/SQL query statement below */selectname,sum(nvl(b.amount, 0)) balance from Users aleft join Transactions bon a.account = b. accountgroup by namehaving sum(nvl(b.amount, 0))>10000

【LeetCode-SQL专项突破】-第10天:过滤

1084. 销售分析III

🚀 Table: Product+--------------+---------+| Column Name  | Type    |+--------------+---------+| product_id   | int     || product_name | varchar || unit_price   | int     |+--------------+---------+Product_id是该表的主键。该表的每一行显示每个产品的名称和价格。Table: Sales+-------------+---------+| Column Name | Type    |+-------------+---------+| seller_id   | int     || product_id  | int     || buyer_id    | int     || sale_date   | date    || quantity    | int     || price| int     |+------ ------+---------+这个表没有主键，它可以有重复的行。product_id 是 Product 表的外键。该表的每一行包含关于一个销售的一些信息。🚀 需求编写一个SQL查询，报告2019年春季才售出的产品。即仅在2019-01-01至2019-03-31（含）之间出售的商品。以任意顺序 返回结果表。查询结果格式如下所示。示例 1:输入：Product table:+------------+--------------+------------+| product_id | product_name | unit_price |+------------+--------------+------------+| 1   | S8    | 1000|| 2   | G4    | 800 || 3   | iPhone| 1400|+------------+--------------+------------+Sales table:+-----------+------------+----------+------------+----------+-------+| seller_id | product_id | buyer_id | sale_date  | quantity | price |+-----------+------------+----------+------------+----------+-------+| 1  | 1   | 1 | 2019-01-21 | 2 | 2000  || 1  | 2   | 2 | 2019-02-17 | 1 | 800   || 2  | 2   | 3 | 2019-06-02 | 1 | 800   || 3  | 3   | 4 | 2019-05-13 | 2 | 2800  |+-----------+------------+----------+------------+----------+-------+输出：+-------------+--------------+| product_id  | product_name |+-------------+--------------+| 1    | S8    |+-------------+--------------+解释:id为1的产品仅在2019年春季销售。id为2的产品在2019年春季销售，但也在2019年春季之后销售。id为3的产品在2019年春季之后销售。我们只返回产品1，因为它是2019年春季才销售的产品。🐴🐴 答案# Write your MySQL query statement belowselect p.product_id, p.product_namefrom Product p, Sales swhere p.product_id = s.product_idgroup by p.product_id, p.product_namehaving(sum(sale_date between '2019-01-01' and '2019-03-31') = count(*))/* Write your T-SQL query statement below */SELECT s.product_id , product_nameFROM Sales s JOIN Product pON s.product_id = p.product_idGROUP BY s.product_id,product_nameHAVING MIN(sale_date) >= '2019-01-01' AND MAX(sale_date) <= '2019-03-31'/* Write your PL/SQL query statement below */SELECT s.product_id "product_id", product_name "product_name"FROM Sales s JOIN Product pON s.product_id = p.product_idGROUP BY s.product_id,product_nameHAVING MIN(sale_date) >= '2019-01-01' AND MAX(sale_date) <= '2019-03-31'