时间戳

首页 > 解决方案 > 使用 MySQL 5.7 将 2 个登录/注销时间表合并为 1 个

mysql - 使用 MySQL 5.7 将 2 个登录/注销时间表合并为 1 个

问题描述

我有两个表，每个表都有用户名和日期时间列，一个用于登录时间，另一个用于注销。我遇到了和我一样的问题，在这个问题中描述：基于时间合并两个表，这个很好的解决方案：

select
    id,
    min(case when action = 'in'  then dt end) login_time,
    max(case when action = 'out' then dt end) logout_time
from (
    select
        t.*,
        sum(case when action = 'in' then 1 else 0 end)
            over(partition by id order by dt) grp
    from (
        select id, login_time dt, 'in' action from login
        union all select id, logout_time, 'out' from logout
    ) t
) t
group by id, grp
order by id, grp

但是这个解决方案使用“OVER”子句，在 MySQL5.7 中不支持。任何人都可以帮我转换这个相同的逻辑，但在 MySQL5.7 中没有“OVER”子句和支持。

标签： mysqlsql

解决方案

一种方法使用变量：

select id,
       min(case when action = 'in'  then dt end) as login_time,
       max(case when action = 'out' then dt end) as logout_time
from (select t.*,
             (@grp := if(@id = id, @grp,
                         if(@id := id, @grp + 1, @grp + 1)
                        )
             ) as grp
      from (select id, login_time as dt, 'in' as action
            from login
            union all
            select id, logout_time, 'out'
            from logout
            order by 1, 2
           ) t cross join
           (select @id := -1, @grp := -1) params
     ) t
group by id, grp
order by id, grp;

注意：如果id是一个字符串，那么@id应该初始化为''而不是-1。

请注意，语句中的变量赋值SELECT在 MySQL 8+ 中已被弃用。你真的应该升级到 MySQL 8+ 并学习窗口函数。

推荐阅读