时间戳

首页 > 解决方案 > Sql按userId查询前两行分组

sql - Sql按userId查询前两行分组

问题描述

我有这样的数据

deviceId | userId | Time
20       | 1      | 2-Jan-18
21       | 1      | 2-Jan-19
22       | 1      | 2-Jan-10
30       | 2      | 2-Jan-18
30       | 2      | 2-Jan-19

我想查询用户使用的 2 个第一台设备以及时间（按 userId 分组）

userId|firstDeviceId|firstDeviceTime|secondDeviceId|secondDeviceTime
1     |20           | 2-Jan-18      | 21           | 2-Jan-19
2     |30           | 2-Jan-18      | 30           | 2-Jan-19

各位大神能告诉我怎么做吗？谢谢

标签： sql

解决方案

一个典型的方法使用row_number()和条件聚合：

select userid,
       max(case when seqnum = 1 then deviceid end) as deviceid_1,
       max(case when seqnum = 1 then devicetime end) as devicetime_1,
       max(case when seqnum = 2 then deviceid end) as deviceid_2,
       max(case when seqnum = 2 then devicetime end) as devicetime_2
from (select t.*, row_number() over (partition by userid order by device time desc) as seqnum
      from t
     ) t
where seqnum <= 2
group by userid;

推荐阅读