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' store
from Products
union all
select product_id, store2 price , 'store2' store
from Products
union all
select product_id, store3 price , 'store3' store
from Products
)
select product_id , store, price
from tep
where price is not null2. 力扣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 category
from Accounts
), temp as (
-- 自己union all创建一张表
select 'Low Salary' category
union all
select 'Average Salary' category
union all
select '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 category3. 力扣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_id
from 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_id
from Orders
where product_name = 'C'
), tep3 as (
-- 然后查询到有A有B没C的人
select distinct customer_id
from tep1
where customer_id not in (
select customer_id
from tep2
)
)
-- 然后跟Customers 表jion
select t.customer_id, customer_name
from tep3 t
join Customers c
on t.customer_id = c.customer_id4. 力扣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 RequestAccepted
union all
select accepter_id id
from RequestAccepted
)
-- 然后使用子查询找到好友最多的人
select id, count(*) num
from tep
group by id
having count(*) >= all (
select count(*)
from tep
group by id
)
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
