Question

2922. 市场分析 III

English Version

题目描述

表： Users

+----------------+---------+
| Column Name  | Type  |
+----------------+---------+
| seller_id    | int   |
| join_date    | date  |
| favorite_brand | varchar |
+----------------+---------+
seller_id 是该表具有唯一值的列。
该表包含卖家的 ID, 加入日期以及最喜欢的品牌。

表： Items

+---------------+---------+
| Column Name   | Type  |
+---------------+---------+
| item_id     | int   |
| item_brand  | varchar |
+---------------+---------+
item_id 是该表具有唯一值的列。
该表包含商品 ID 和商品品牌。

表： Orders

+---------------+---------+
| Column Name   | Type  |
+---------------+---------+
| order_id    | int   |
| order_date  | date  |
| item_id     | int   |
| seller_id   | int   |
+---------------+---------+
order_id 是该表具有唯一值的列。
item_id 是指向 Items 表的外键。
seller_id 是指向 Users 表的外键。
该表包含订单 ID、下单日期、商品 ID 和卖家 ID。

编写一个解决方案，找到卖出非喜爱的品牌数量 最多 的一个卖家。如果有多个卖家销售了同样数量的商品，则返回包括所有卖出非喜爱品牌数量最多的卖家名单。 

返回按 seller_id_ 升序排序 的结果表。_

结果格式如下示例所示。

 

示例 1:

输入：
Users table:
+-----------+------------+----------------+
| seller_id | join_date  | favorite_brand |
+-----------+------------+----------------+
| 1     | 2019-01-01 | Lenovo     |
| 2     | 2019-02-09 | Samsung    |
| 3     | 2019-01-19 | LG       |
+-----------+------------+----------------+
Orders table:
+----------+------------+---------+-----------+
| order_id | order_date | item_id | seller_id |
+----------+------------+---------+-----------+
| 1    | 2019-08-01 | 4     | 2     |
| 2    | 2019-08-02 | 2     | 3     |
| 3    | 2019-08-03 | 3     | 3     |
| 4    | 2019-08-04 | 1     | 2     |
| 5    | 2019-08-04 | 4     | 2     |
+----------+------------+---------+-----------+
Items table:
+---------+------------+
| item_id | item_brand |
+---------+------------+
| 1     | Samsung  |
| 2     | Lenovo   |
| 3     | LG     |
| 4     | HP     |
+---------+------------+
输出：
+-----------+-----------+
| seller_id | num_items |
+-----------+-----------+
| 2     | 1     |
| 3     | 1     |
+-----------+-----------+
解释：
- 卖家 ID 为 2 的用户销售了三件商品，但只有两件不是他最喜欢的商品。由于这两个商品品牌相同，所以我们只计数 1。 
- 卖家 ID 为 3 的用户销售了两件商品，但只有一件不是他最喜欢的商品。我们将只把 不被标记为最喜欢 的商品列入计数中。
因为卖家 ID 为 2 和 3 的卖家都有一件商品列入计数，所以他们都将显示在输出中。

解法

方法一：等值连接 + 分组 + 子查询

我们可以使用等值连接，将 Orders 表和 Users 表按照 seller_id 进行连接，接着再按照 item_id 连接 Items，筛选出 item_brand 不等于 favorite_brand 的记录，然后按照 seller_id 进行分组，统计每个 seller_id 对应的 item_id 的个数，最后再使用子查询，找出 item_id 个数最多的 seller_id。

# Write your MySQL query statement below
WITH
  T AS (
    SELECT seller_id, COUNT(DISTINCT item_id) AS num_items
    FROM
      Orders
      JOIN Users USING (seller_id)
      JOIN Items USING (item_id)
    WHERE item_brand != favorite_brand
    GROUP BY 1
  )
SELECT seller_id, num_items
FROM T
WHERE num_items = (SELECT MAX(num_items) FROM T)
ORDER BY 1;