LeetCode MySQL 1159. 市场分析 II（rank+over）

1. 题目

表: 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 表的外键
buyer_id 和 seller_id 是 Users 表的外键

表: Items

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| item_id       | int     |
| item_brand    | varchar |
+---------------+---------+
item_id 是该表的主键

写一个 SQL 查询确定每一个用户按日期顺序卖出的第二件商品的品牌是否是他们最喜爱的品牌。

如果一个用户卖出少于两件商品，查询的结果是 no 。

题目保证没有一个用户在一天中卖出超过一件商品

下面是查询结果格式的例子：

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         |
+---------+------------+

Result table:
+-----------+--------------------+
| seller_id | 2nd_item_fav_brand |
+-----------+--------------------+
| 1         | no                 |
| 2         | yes                |
| 3         | yes                |
| 4         | no                 |
+-----------+--------------------+

id 为 1 的用户的查询结果是 no，
		因为他什么也没有卖出
id为 2 和 3 的用户的查询结果是 yes，
		因为他们卖出的第二件商品的品牌是他们自己最喜爱的品牌
id为 4 的用户的查询结果是 no，
		因为他卖出的第二件商品的品牌不是他最喜爱的品牌

来源：力扣（LeetCode） 链接：https://leetcode-cn.com/problems/market-analysis-ii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

2. 解题

• 连接各表，窗口函数求出排名

select o.order_date, o.seller_id, i.item_brand, u.favorite_brand,
        rank() over(partition by o.seller_id order by o.order_date) rnk
from Orders o 
left join Users u
on o.seller_id = u.user_id
left join Items i
on o.item_id = i.item_id

{"headers": ["order_date", "seller_id", "item_brand", "favorite_brand", "rnk"], 
"values": [["2019-08-01", 2, "HP", "Samsung", 1], 
			["2019-08-04", 2, "Samsung", "Samsung", 2], 
			["2019-08-02", 3, "Lenovo", "LG", 1], 
			["2019-08-03", 3, "LG", "LG", 2], 
			["2019-08-04", 4, "Samsung", "HP", 1], 
			["2019-08-05", 4, "Lenovo", "HP", 2]]}

• 选出 rnk = 2 的，再左连接 所有用户的 id

# Write your MySQL query statement below
select u.seller_id, if(item_brand is null, 'no', 
                        case when favorite_brand = item_brand then 'yes'
                             else 'no' end) as 2nd_item_fav_brand
from
(
    select user_id as seller_id
    from Users
) u
left join
(
    select *
    from
    (
        select o.order_date, o.seller_id, i.item_brand, u.favorite_brand,
                rank() over(partition by o.seller_id order by o.order_date) rnk
        from Orders o 
        left join Users u
        on o.seller_id = u.user_id
        left join Items i
        on o.item_id = i.item_id
    ) t1
    where rnk = 2
) t2
on u.seller_id = t2.seller_id

---

我的CSDN博客地址 https://michael.blog.csdn.net/

长按或扫码关注我的公众号（Michael阿明），一起加油、一起学习进步！