mysql - SQL按用户选择第二小的值

Question

我想得到一个只包含第二次购买日期的 list

我有以下列表:

    Order_id     User_id       Purchase_date
    1            1             01/01/2018
    2            1             02/01/2018
    3            1             02/01/2018
    4            2             03/01/2018
    5            2             03/01/2018
    6            3             01/01/2018
    7            3             02/01/2018

期望的结果:

    user_id       2nd_purchase_date
    1             02/01/20181 
    2             03/01/2018
    3             02/01/2018

谢谢

Answer 1

SQL DEMO

你可以像下面那样做:

select user_id, Purchase_date AS '2nd_purchase_date'
from yourtable
where order_id in (
    select min(order_id)
    from yourtable T1
    where order_id > (
        select min(order_id)
        from yourtable
        where user_id = T1.user_id
    )
    group by user_id
)