前言
SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。
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 below select Email from Person group by Email having 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
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 below select actor_id,director_id from ActorDirector group by actor_id,director_id having count(timestamp)>=3 /* Write your T-SQL query statement below */ select actor_id,director_id from ActorDirector group by actor_id,director_id having count(timestamp)>=3 /* Write your PL/SQL query statement below */ select actor_id "actor_id", director_id "director_id" from ActorDirector group by actor_id,director_id having count(*) > =3
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 below WITH t AS ( SELECT account, SUM(amount) balance FROM Transactions GROUP BY account HAVING SUM(amount)>10000 ) SELECT name, balance FROM t JOIN Users USING(account) /* Write your T-SQL query statement below */ select name, sum(isnull(b.amount, 0)) balance from Users a left join Transactions b on a.account = b. account group by name having sum(isnull(b.amount, 0))>10000 /* Write your PL/SQL query statement below */ select name, sum(nvl(b.amount, 0)) balance from Users a left join Transactions b on a.account = b. account group by name having sum(nvl(b.amount, 0))>10000
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 below select p.product_id, p.product_name from Product p, Sales s where p.product_id = s.product_id group by p.product_id, p.product_name having(sum(sale_date between '2019-01-01' and '2019-03-31') = count(*)) /* Write your T-SQL query statement below */ SELECT s.product_id , product_name FROM Sales s JOIN Product p ON s.product_id = p.product_id GROUP BY s.product_id,product_name HAVING 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 p ON s.product_id = p.product_id GROUP BY s.product_id,product_name HAVING MIN(sale_date) >= '2019-01-01' AND MAX(sale_date) <= '2019-03-31'
