持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第11天，点击查看活动详情

📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程😜😜😜
擅长Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理。
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️

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

前言

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'

相关推荐阅读：
MySQL8.0新特性抢先看，性能又双叒提升了
MySQL武林秘籍，SQL学废必过考试
Linux7.6源码安装Mysql8
Oracle巡检脚本大全，服务器可直接部署
MySQL root密码忘记找回妙招
监控神器Zabbix，从部署到应用
Oracle监听日志清除

体系化学习SQL，请关注CSDN博客 blog.csdn.net/weixin_4164…