目录
题目链接(无VIP请直接看下面的需求)
- 链接: 15分钟没思路建议直接看答案
题目和题目代码
表: Users
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| user_id | int |
| join_date | date |
| favorite_brand | varchar |
+----------------+---------+
user_id 是该表的主键(具有唯一值的列)。
表中包含一位在线购物网站用户的个人信息，用户可以在该网站出售和购买商品。
表: Orders
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| order_id | int |
| order_date | date |
| item_id | int |
| buyer_id | int |
| seller_id | int |
+---------------+---------+
order_id 是该表的主键(具有唯一值的列)。
item_id 是 Items 表的外键(reference 列)。
buyer_id 和 seller_id 是 Users 表的外键。
表: Items
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| item_id | int |
| item_brand | varchar |
+---------------+---------+
item_id 是该表的主键(具有唯一值的列)。
编写一个解决方案，为每个用户找出他们出售的第二件商品(按日期)的品牌是否是他们最喜欢的品牌。如果用户售出的商品少于两件，则该用户的结果为否。保证卖家不会在一天内卖出一件以上的商品。
以 任意顺序 返回结果表。
返回结果格式如下例所示：
示例 1：
输入：
Users table:
+---------+------------+----------------+
| user_id | join_date | favorite_brand |
+---------+------------+----------------+
| 1 | 2019-01-01 | Lenovo |
| 2 | 2019-02-09 | Samsung |
| 3 | 2019-01-19 | LG |
| 4 | 2019-05-21 | HP |
+---------+------------+----------------+
Orders table:
+----------+------------+---------+----------+-----------+
| order_id | order_date | item_id | buyer_id | seller_id |
+----------+------------+---------+----------+-----------+
| 1 | 2019-08-01 | 4 | 1 | 2 |
| 2 | 2019-08-02 | 2 | 1 | 3 |
| 3 | 2019-08-03 | 3 | 2 | 3 |
| 4 | 2019-08-04 | 1 | 4 | 2 |
| 5 | 2019-08-04 | 1 | 3 | 4 |
| 6 | 2019-08-05 | 2 | 2 | 4 |
+----------+------------+---------+----------+-----------+
Items table:
+---------+------------+
| item_id | item_brand |
+---------+------------+
| 1 | Samsung |
| 2 | Lenovo |
| 3 | LG |
| 4 | HP |
+---------+------------+
输出：
+-----------+--------------------+
| seller_id | 2nd_item_fav_brand |
+-----------+--------------------+
| 1 | no |
| 2 | yes |
| 3 | yes |
| 4 | no |
+-----------+--------------------+
解释：
id 为 1 的用户的查询结果是 no，因为他什么也没有卖出
id为 2 和 3 的用户的查询结果是 yes，因为他们卖出的第二件商品的品牌是他们最喜爱的品牌
id为 4 的用户的查询结果是 no，因为他卖出的第二件商品的品牌不是他最喜爱的品牌
Create table If Not Exists Users (user_id int, join_date date, favorite_brand varchar(10))
Create table If Not Exists Orders (order_id int, order_date date, item_id int, buyer_id int, seller_id int)
Create table If Not Exists Items (item_id int, item_brand varchar(10))
Truncate table Users
insert into Users (user_id, join_date, favorite_brand) values ('1', '2019-01-01', 'Lenovo')
insert into Users (user_id, join_date, favorite_brand) values ('2', '2019-02-09', 'Samsung')
insert into Users (user_id, join_date, favorite_brand) values ('3', '2019-01-19', 'LG')
insert into Users (user_id, join_date, favorite_brand) values ('4', '2019-05-21', 'HP')
Truncate table Orders
insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('1', '2019-08-01', '4', '1', '2')
insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('2', '2019-08-02', '2', '1', '3')
insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('3', '2019-08-03', '3', '2', '3')
insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('4', '2019-08-04', '1', '4', '2')
insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('5', '2019-08-04', '1', '3', '4')
insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('6', '2019-08-05', '2', '2', '4')
Truncate table Items
insert into Items (item_id, item_brand) values ('1', 'Samsung')
insert into Items (item_id, item_brand) values ('2', 'Lenovo')
insert into Items (item_id, item_brand) values ('3', 'LG')
insert into Items (item_id, item_brand) values ('4', 'HP')
1.读题(建议使用这种表结构和数据对比看阅读)
______________________________________________
编写一个解决方案，为每个用户找出他们 出售的第二件商品(按日期) 的品牌是否是他们最喜欢的品牌。如果用户售出的商品少于两件，则该用户的结果为否。保证卖家不会在一天内卖出一件以上的商品(意思是日期唯一的)。
2.答案代码以及图表解释
+--------+----------+-------+--------+---------+ +-------+----------+--------------+
|order_id|order_date|item_id|buyer_id|seller_id| |user_id|join_date |favorite_brand|
|秩序_id |顺序_日期|项目_id |买家_id |卖家_id | |用户_id |加入_ |最喜欢的日期_品牌|
+--------+----------+-------+--------+---------+ +-------+----------+--------------+
|1 |2019-08-01|4 |1 |2 | |1 |2019-01-01|Lenovo |
|2 |2019-08-02|2 |1 |3 | |2 |2019-02-09|Samsung |
|3 |2019-08-03|3 |2 |3 | |3 |2019-01-19|LG |
|4 |2019-08-04|1 |4 |2 | |4 |2019-05-21|HP |
|5 |2019-08-04|1 |3 |4 | +-------+----------+--------------+
|6 |2019-08-05|2 |2 |4 |
+--------+----------+-------+--------+---------+ ;
+-------+----------+
|item_id|item_brand|
|项目_id |项目_品牌|
+-------+----------+
|1 |Samsung |
|2 |Lenovo |
|3 |LG |
|4 |HP |
+-------+----------+;
with a1 as (
select order_date, seller_id, item_brand
from orders o
join items i on o.item_id = i.item_id)
,a2 as (
select user_id,
dense_rank() over (partition by user_id order by order_date) dr
,if(dense_rank() over (partition by user_id order by order_date)=2 and favorite_brand=item_brand
,'yes','no') 2nd_item_fav_brand
from users u
join a1 on u.user_id = a1.seller_id)
select
u.user_id seller_id,
if(2nd_item_fav_brand is null,'no',2nd_item_fav_brand) 2nd_item_fav_brand
from users u
left join (select user_id,2nd_item_fav_brand from a2 where dr=2) a22
on a22.user_id=u.user_id;
-------------------------------------------------------------------------------------------------
+--------+----------+-------+--------+---------+-------+----------+with a1 as (
|order_id|order_date|item_id|buyer_id|seller_id|item_id|item_brand|select order_date, seller_id, item_brand
|秩序_id |顺序_日期 |项目_id |买家_id |卖家_id |项目_id |项目_品牌| from orders o
+--------+----------+-------+--------+---------+-------+----------+join items i on o.item_id = i.item_id)
|1 |2019-08-01|4 |1 |2 |4 |HP |
|2 |2019-08-02|2 |1 |3 |2 |Lenovo |
|3 |2019-08-03|3 |2 |3 |3 |LG |
|4 |2019-08-04|1 |4 |2 |1 |Samsung |
|5 |2019-08-04|1 |3 |4 |1 |Samsung |
|6 |2019-08-05|2 |2 |4 |2 |Lenovo |
+--------+----------+-------+--------+---------+-------+----------+
-------------------------------------------------------------------------------
,a2 as (
select user_id,
dense_rank() over (partition by user_id order by order_date) dr
,if(dense_rank() over (partition by user_id order by order_date)=2 and favorite_brand=item_brand
,'yes','no') 2nd_item_fav_brand
from users u
join a1 on u.user_id = a1.seller_id);
+-------+----------+--------------+--------+----------+--------+---------+-------+----------+--+------------------+
|user_id|join_date |favorite_brand|order_id|order_date|buyer_id|seller_id|item_id|item_brand|dr|2nd_item_fav_brand|
|用户_id |加入_ |最喜欢的日期_品牌|秩序_id |顺序_日期 |买家_id |卖家_id |项目_id |项目_品牌|
+-------+----------+--------------+--------+----------+--------+---------+-------+----------+--+------------------+
|1 |2019-01-01|Lenovo |null |null |null |null |null |null |1 |no |
|2 |2019-02-09|Samsung |1 |2019-08-01|1 |2 |4 |HP |1 |no |
|2 |2019-02-09|Samsung |4 |2019-08-04|4 |2 |1 |Samsung |2 |yes |
|3 |2019-01-19|LG |2 |2019-08-02|1 |3 |2 |Lenovo |1 |no |
|3 |2019-01-19|LG |3 |2019-08-03|2 |3 |3 |LG |2 |yes |
|4 |2019-05-21|HP |5 |2019-08-04|3 |4 |1 |Samsung |1 |no |
|4 |2019-05-21|HP |6 |2019-08-05|2 |4 |2 |Lenovo |2 |no |
+-------+----------+--------------+--------+----------+--------+---------+-------+----------+--+------------------+
上下表关联left join
+-------+----------+--------------+select
|user_id|join_date |favorite_brand| u.user_id seller_id,
用户_id |加入_ |最喜欢的日期_品牌| if(2nd_item_fav_brand is null,'no',2nd_item_fav_brand) 2nd_item_fav_brand
+-------+----------+--------------+from users u
|1 |2019-01-01|Lenovo |left join (select user_id,2nd_item_fav_brand from a2 where dr=2) a22
|2 |2019-02-09|Samsung |on a22.user_id=u.user_id;
|3 |2019-01-19|LG |
|4 |2019-05-21|HP |
+-------+----------+--------------+
输出结果: +---------+------------------+
|seller_id|2nd_item_fav_brand|
+---------+------------------+
|1 |no |
|2 |yes |
|3 |yes |
|4 |no |
+---------+------------------+
或者直接3join,但是分步减字段join效率高一些;
with
a2 as (
select user_id,
dense_rank() over (partition by user_id order by order_date) dr
,if(dense_rank() over (partition by user_id order by order_date)=2 and favorite_brand=item_brand
,'yes','no') 2nd_item_fav_brand
from orders o
join items i on o.item_id = i.item_id
join users u on u.user_id = o.seller_id)
select
u.user_id seller_id,
if(2nd_item_fav_brand is null,'no',2nd_item_fav_brand) 2nd_item_fav_brand
from users u
left join (select user_id,2nd_item_fav_brand from a2 where dr=2) a22
on a22.user_id=u.user_id;
不能用item_id去判断，这题item_id不同，品牌名称却可能相同,吐了
