SQL最近三个日期所有订单

订单信息表：  order_info
字段： user_id create_date total_amount

需求：查询出每个用户的最近三个日期所有订单

-- 方法一
select two.* from (
    select user_id,create_date from (
        select user_id,create_date,row_number() over(partition by user_id order by create_date desc) drnk from (
            select distinct user_id,create_date from order_info
        ) a 
    ) b where b.drnk <= 3

)  one  join (
    select order_id, user_id,create_date from order_info
) two on one.user_id = two.user_id and one.create_date = two.create_date
;


-- 方法二
-- 用dense_rank 
select order_id,user_id,create_date from (
    select order_id,user_id,create_date,dense_rank() over(partition by user_id order by create_date desc) drnk from order_info
) a where a.drnk <= 3