【力扣 | SQL题 | 每日3题】力扣1795，1907，1398，602

1. 力扣1795：每个产品在不同商品的价格

1.1 题目：

表：Products

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| store1      | int     |
| store2      | int     |
| store3      | int     |
+-------------+---------+
在 SQL 中，这张表的主键是 product_id（产品Id）。
每行存储了这一产品在不同商店 store1, store2, store3 的价格。
如果这一产品在商店里没有出售，则值将为 null。

请你重构 Products 表，查询每个产品在不同商店的价格，使得输出的格式变为(product_id, store, price) 。如果这一产品在商店里没有出售，则不输出这一行。

输出结果表中的 顺序不作要求 。

查询输出格式请参考下面示例。

示例 1：

输入：
Products table:
+------------+--------+--------+--------+
| product_id | store1 | store2 | store3 |
+------------+--------+--------+--------+
| 0          | 95     | 100    | 105    |
| 1          | 70     | null   | 80     |
+------------+--------+--------+--------+
输出：
+------------+--------+-------+
| product_id | store  | price |
+------------+--------+-------+
| 0          | store1 | 95    |
| 0          | store2 | 100   |
| 0          | store3 | 105   |
| 1          | store1 | 70    |
| 1          | store3 | 80    |
+------------+--------+-------+
解释：
产品 0 在 store1、store2、store3 的价格分别为 95、100、105。
产品 1 在 store1、store3 的价格分别为 70、80。在 store2 无法买到。

1.2 思路：

union all将原表扁平化处理，然后再删选字段。

1.3 题解：

-- 利用union all将Products扁平化，并添加store字段
with tep as (select product_id, store1 price, 'store1' storefrom Productsunion allselect product_id, store2 price , 'store2' storefrom Productsunion allselect product_id, store3 price , 'store3' storefrom Products
)
select product_id , store, price
from tep
where price is not null

2. 力扣1907：按分类统计薪水

2.1 题目：

表: Accounts

+-------------+------+
| 列名        | 类型  |
+-------------+------+
| account_id  | int  |
| income      | int  |
+-------------+------+
在 SQL 中，account_id 是这个表的主键。
每一行都包含一个银行帐户的月收入的信息。

查询每个工资类别的银行账户数量。 工资类别如下：

• "Low Salary"：所有工资 严格低于 20000 美元。
• "Average Salary"： 包含 范围内的所有工资 [$20000, $50000] 。

• "High Salary"：所有工资 严格大于 50000 美元。

结果表 必须 包含所有三个类别。 如果某个类别中没有帐户，则报告 0 。

按 任意顺序 返回结果表。

查询结果格式如下示例。

示例 1：

输入：
Accounts 表:
+------------+--------+
| account_id | income |
+------------+--------+
| 3          | 108939 |
| 2          | 12747  |
| 8          | 87709  |
| 6          | 91796  |
+------------+--------+
输出：
+----------------+----------------+
| category       | accounts_count |
+----------------+----------------+
| Low Salary     | 1              |
| Average Salary | 0              |
| High Salary    | 3              |
+----------------+----------------+
解释：
低薪: 有一个账户 2.
中等薪水: 没有.
高薪: 有三个账户，他们是 3, 6和 8.

2.2 思路：

看注释。

2.3 题解：

-- 建立tep临时表给Accounts添加一个字段category
with tep as (select income, case when income < 20000 then 'Low Salary'when income <= 50000 then 'Average Salary'else 'High Salary'end categoryfrom Accounts 
), temp as (-- 自己union all创建一张表select 'Low Salary' categoryunion allselect 'Average Salary' categoryunion allselect 'High Salary' category
)
-- 然后表join连接。
-- 当income为null，说明驱动表某一记录没有跟被驱动表任何一个记录连接
-- 否则就返回分组的个数
select t1.category, if(income is null, 0, count(*)) accounts_count 
from temp t1
left join tep t2
on t1.category = t2.category
group by category

3. 力扣1398：购买了产品A和产品B却没有购买产品C的顾客

3.1 题目：

Customers 表：

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| customer_id         | int     |
| customer_name       | varchar |
+---------------------+---------+
customer_id 是这张表中具有唯一值的列。
customer_name 是顾客的名称。

Orders 表：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| customer_id   | int     |
| product_name  | varchar |
+---------------+---------+
order_id 是这张表中具有唯一值的列。
customer_id 是购买了名为 "product_name" 产品顾客的id。

请你编写解决方案，报告购买了产品 "A"，"B" 但没有购买产品 "C" 的客户的 customer_id 和 customer_name，因为我们想推荐他们购买这样的产品。

返回按 customer_id 排序 的结果表。

返回结果格式如下所示。

示例 1：

输入：
Customers table:
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 1           | Daniel        |
| 2           | Diana         |
| 3           | Elizabeth     |
| 4           | Jhon          |
+-------------+---------------+Orders table:
+------------+--------------+---------------+
| order_id   | customer_id  | product_name  |
+------------+--------------+---------------+
| 10         |     1        |     A         |
| 20         |     1        |     B         |
| 30         |     1        |     D         |
| 40         |     1        |     C         |
| 50         |     2        |     A         |
| 60         |     3        |     A         |
| 70         |     3        |     B         |
| 80         |     3        |     D         |
| 90         |     4        |     C         |
+------------+--------------+---------------+
输出：
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 3           | Elizabeth     |
+-------------+---------------+
解释：
只有 customer_id 为 3 的顾客购买了产品 A 和产品 B ，却没有购买产品 C 。

3.2 思路：

看注释。

3.3 题解：

-- 然后找自连接找到有a有b的人
with tep1 as (select o1.customer_idfrom Orders o1 join Orders o2 on o1.customer_id = o2.customer_id  and (o1.product_name, o2.product_name) in (('A', 'B'), ('B', 'A'))
), tep2 as (-- 先查出买了c产品的人select customer_idfrom Orders where product_name = 'C'
), tep3 as (-- 然后查询到有A有B没C的人select distinct customer_idfrom tep1where customer_id not in (select customer_idfrom tep2)
)
-- 然后跟Customers 表jion
select t.customer_id, customer_name 
from tep3 t
join Customers c
on t.customer_id = c.customer_id

4. 力扣602：好友申请2：谁有最多的好友

4.1 题目：

RequestAccepted 表：

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| requester_id   | int     |
| accepter_id    | int     |
| accept_date    | date    |
+----------------+---------+
(requester_id, accepter_id) 是这张表的主键(具有唯一值的列的组合)。
这张表包含发送好友请求的人的 ID ，接收好友请求的人的 ID ，以及好友请求通过的日期。

编写解决方案，找出拥有最多的好友的人和他拥有的好友数目。

生成的测试用例保证拥有最多好友数目的只有 1 个人。

查询结果格式如下例所示。

示例 1：

输入：
RequestAccepted 表：
+--------------+-------------+-------------+
| requester_id | accepter_id | accept_date |
+--------------+-------------+-------------+
| 1            | 2           | 2016/06/03  |
| 1            | 3           | 2016/06/08  |
| 2            | 3           | 2016/06/08  |
| 3            | 4           | 2016/06/09  |
+--------------+-------------+-------------+
输出：
+----+-----+
| id | num |
+----+-----+
| 3  | 3   |
+----+-----+
解释：
编号为 3 的人是编号为 1 ，2 和 4 的人的好友，所以他总共有 3 个好友，比其他人都多。

进阶：在真实世界里，可能会有多个人拥有好友数相同且最多，你能找到所有这些人吗？

4.2 思路：

union all将表扁平化处理，然后查找到谁有最多的好友，count计算这个人的好友有多少个。

4.3 题解：

-- 炸裂，并用union all连接起来
with tep as (select requester_id id from RequestAcceptedunion allselect accepter_id id from RequestAccepted
)-- 然后使用子查询找到好友最多的人
select id, count(*) num
from tep
group by id
having count(*) >= all (select count(*)from tepgroup by id
)