方法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）
著作權歸作者所有。商業轉載請聯系作者獲得授權，非商業轉載請注明出處。