Mysql:事件表:按时间顺序连续连接

Question

我有下表：

user_id | Membership_type | start_Date
1       |         1       |      1
1       |         1       |      2
1       |         2       |      3
1       |         3       |      4

对于多个用户，我需要找出每个用户的成员类型何时更改以及更改是什么，格式如下(开始日期是日期时间，为了便于理解，请将其放在这里的int中)

user_id |Membership_change| change_Date
1       |      1 to 2     |     3
1       |      2 to 3     |     4

我试过了

select m1.user_id, concat(m1.Membership_type, ' to ',m2.Membership_type), m2.start_date
from table_membership m1 
join table_membership m2
on m1.user_id=m2.user_id and m1.start_date<m2.start_date and m1.membership_type<>m2.membership_type

但这会回来的

user_id |Membership_change| change_Date
1       |      1 to 2     |     3
1       |      1 to 2     |     3
1       |      1 to 3     |     4
1       |      2 to 3     |     4

通过分组删除重复的1到2不是问题，但我似乎想不出一种方法来避免1到3的结果。我基本上只需要按时间顺序从一个成员加入到下一个成员。

任何想法都将不胜感激！

编辑:我有一个想法，就是添加列m1.start_date并按account_id和m1.start_date分组，这样我就只会得到连接每个条目的第一行。在连接之前也要按日期进行预排序，以确保它们都是有序的。将进行测试。

Answer 1

我有一个想法，就是添加列m1.start_date并按account_id和m1.start_date分组，这样我就只会得到连接每个条目的第一行。在连接之前也要按日期进行预排序，以确保它们都是有序的。

select m.user_id, m.membership_change, m.change_date from
(
select
  m1.user_id,
  concat(m1.Membership_type, ' to ',m2.Membership_type) as membership_change,
  m2.start_date as change_date, 
m1.start_date
from (select * from table_membership order by start_date asc)m1
  join (select * from table_membership order by start_date asc)m2
    on m1.user_id = m2.user_id
      and m1.start_date < m2.start_date
      and m1.membership_type <> m2.membership_type
GROUP BY m1.user_id,  m1.start_Date
)m group by 1,2,3

Answer 2

您缺少分组依据

select
  m1.user_id,
  concat(m1.Membership_type, ' to ',m2.Membership_type),
  m2.start_date
from table_membership m1
  join table_membership m2
    on m1.user_id = m2.user_id
      and m1.start_date < m2.start_date
      and m1.membership_type <> m2.membership_type
GROUP BY user_id, Membership_change, change_Date