leetcode 2292 连续两年订购商品超过多少次的问题.

 

 

 

方法1 ： 
SELECT distinct o.product_id
FROM (
SELECT product_id, year(purchase_date) year, dense_rank() over(partition by product_id order by year(purchase_date)) rk
FROM Orders 
GROUP BY product_id, year(purchase_date)
HAVING count(*) >= 3) o
GROUP BY o.product_id, o.year-o.rk
HAVING count(o.year) >= 2


方法2： 

# Write your MySQL query statement below
select distinct product_id
from (
    select product_id, purchase_year-rn as sub_year
    from (
        select product_id, purchase_year, row_number() over (partition by product_id order by purchase_year) as rn 
        from (
            select product_id, year(purchase_date) as purchase_year
            from Orders
            group by product_id,purchase_year
            having count(*)>=3  -- 这一层是连续n年 大于购买三个的数量
        ) t1
    ) t2
    group by product_id, sub_year
    having count(*)>=2 --- 这一层是至少连续两年的意思.
) t3

作者：Sleepy NightingalemKj
链接：https://leetcode.cn/problems/products-with-three-or-more-orders-in-two-consecutive-years/solutions/2757982/lian-xu-lei-wen-ti-de-qiu-jie-si-lu-by-s-cmyj/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

| product_id | YEAR | rk | COUNT(a.order_id) |
| ---------- | ---- | -- | ----------------- |
| 1 | 2020 | 1 | 3 |
| 1 | 2021 | 2 | 3 |

 

| order_id | product_id | quantity | purchase_date |
| -------- | ---------- | -------- | ------------- |
| 1 | 1 | 7 | 2020-03-16 |
| 2 | 1 | 4 | 2020-12-02 |
| 3 | 1 | 7 | 2020-05-10 |
| 4 | 1 | 6 | 2021-12-23 |
| 5 | 1 | 5 | 2021-05-21 |
| 6 | 1 | 6 | 2021-10-11 |
| 7 | 2 | 6 | 2022-10-11 |
收起
输出
| product_id | YEAR | rk |
| ---------- | ---- | -- |
| 1 | 2020 | 1 |
| 1 | 2021 | 2 |
| 2 | 2022 | 1 |

 

 

 

 

 

编写解决方案，获取连续两年订购三次或三次以上的所有产品的 id。

以 任意顺序 返回结果表。

结果格式示例如下。

 

示例 1:

输入: 
Orders 表:
+----------+------------+----------+---------------+
| order_id | product_id | quantity | purchase_date |
+----------+------------+----------+---------------+
| 1        | 1          | 7        | 2020-03-16    |
| 2        | 1          | 4        | 2020-12-02    |
| 3        | 1          | 7        | 2020-05-10    |
| 4        | 1          | 6        | 2021-12-23    |
| 5        | 1          | 5        | 2021-05-21    |
| 6        | 1          | 6        | 2021-10-11    |
| 7        | 2          | 6        | 2022-10-11    |
+----------+------------+----------+---------------+
输出: 
+------------+
| product_id |
+------------+
| 1          |
+------------+
解释: 
产品 1 在 2020 年和 2021 年都分别订购了三次。由于连续两年订购了三次，所以我们将其包含在答案中。
产品 2 在 2022 年订购了一次。我们不把它包括在答案中。