sql - 如何选择 SQL 计数中的第 n 个实例？

Question

我有一个表，其中每一行都有一个用户 ID 和他们开始一个级别的时间戳。

user    timestamp
1   2018-11-04
1   2018-11-07
1   2018-11-09
1   2018-11-09
2   2019-11-02
2   2019-11-03
2   2019-11-06
3   2019-11-10
3   2019-11-13
3   2019-11-15

我需要为用户第二次启动关卡选择时间戳。我试过了:`

select distinct user, timestamp 
from table,
  (select user, count(*) 
   from table
    group by 1
    having count(outcome) > 1) tbl
where table.user = tbl.user and count(*) = 2 

预期结果:

user    timestamp
1   2018-11-07
2   2019-11-03
3   2019-11-13

任何帮助将不胜感激! (如果格式不正确，我深表歉意，这是我的第一个问题。

Answer 1

如果您使用的是支持 CTE 和窗口函数的 DBMS，您可以使用 ROW_NUMBER() 并为每个 user 选择第二行:

WITH CTE AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY user ORDER BY timestamp) AS rn
    FROM times
)
SELECT user, timestamp
FROM CTE
WHERE rn = 2

请注意，您实际上并不需要 CTE，您可以将 CTE 编写为子查询:

SELECT user, timestamp
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY user ORDER BY timestamp) AS rn
    FROM times
) t
WHERE rn = 2

输出

user    timestamp
1       2018-11-07
2       2019-11-03
3       2019-11-13

MySQL 8 demo on dbfiddle