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 年订购了一次。我们不把它包括在答案中。