mysql - 采访问题: convert table login_log to last_login

Question

这是我采访中的一个问题。

在我们的系统中，每个登录事件都会记录到 login_log 表中。主要包含3列:

• 用户 ID
• 登录时间
• login_ip

我们现在有 36,000 个用户，每个用户平均登录大约 100 次，因此该表中有 3,600,000 行。

然后我们要把这个表转换成另一个表，命名为last_login，只记录每个用户的最新登录事件。它还包含 3 列:

• 用户 ID
• 最后登录时间
• last_login_ip

因此这个新表中的行数不会超过 36,000 行。

考生可以通过SQL或编程来解决这个问题。有些考生只用一条 SQL 就解决了。

享受它!

更新

提示:login_log 可能有一个 AUTO_INCRMENT id 作为主键。因此最新登录的事件具有最大的 id。

Answer 1

编辑发现我之前的答案不正确后更新了答案

select
    user_id
    , max(login_time) last_login_time
    , max(login_ip)   last_login_ip 
from 
    (
        select
          user_id
          , login_time 
          , max(login_ip) login_ip
        from
          login_log
        group by user_id, login_time 
    ) t -- this sub-query makes sure we have only one row for each user_id+login_time
group by user_id;   

您可以通过此 link 在 sqlFiddle.com 中找到结果

测试程序——建表:

create table login_log (
  user_id int not null
  , login_time datetime not null
  , login_ip int not null 
  , index(user_id)
  , index(login_time)
);

insert into login_log
values
(1, '2017-02-17', 100)
, (1, '2017-02-15', 200)
, (2, '2017-02-17', 300)
, (2, '2017-02-14', 400)
, (2, '2017-02-17', 500);

我们需要一个子查询(t)来打破平局，以防万一给定的 user_id PLUS login_time 有多于一行组合。子查询确保只有单行从中出现(带有 max(login_ip) 的那个)

我之前的回答忘记了这样一个极端的情况，因此不正确。