sql - 在已经很复杂的 SQL 语句之后，每个用户最近的两条记录(对我来说)

Question

我对 SQL 完全陌生。我有两个表，我通过内部联接连接、排序和过滤。然后我只需要显示按“用户名”分组的两个最近的条目。我不知道该怎么做。我查看了一些发布的解决方案，但我不知道如何将它们应用于我的情况。

这是我现在的位置:

SELECT users.username, logs.event, logs.insertdate
FROM users 
INNER JOIN logs ON users.identifier = logs.useridentifier
WHERE (((logs.event)="Taken" Or (logs.event)="Return"))
ORDER BY users.username, logs.insertdate DESC;

这是我得到的输出示例:

|username | event | insertdate | 
================================
 alan     | Return| 10/15/2014 3:17 PM
 alan     | Taken | 10/15/2014 6:49 AM
 alan     | Return| 10/14/2014 3:16 PM
 alan     | Taken | 10/14/2014 6:50 AM
 alan     | Return| 10/13/2014 3:15 PM
 bill     | Return| 10/15/2014 3:19 PM
 bill     | Taken | 10/15/2014 6:53 AM
 bill     | Return| 10/14/2014 3:26 PM
 bill     | Taken | 10/14/2014 7:00 AM
 bill     | Return| 10/13/2014 3:19 PM

等等。我希望做的是只从每个用户那里获得前两个条目。所以

|username | event | insertdate | 
================================
 alan     | Return| 10/15/2014 3:17 PM
 alan     | Taken | 10/15/2014 6:49 AM
 bill     | Return| 10/15/2014 3:19 PM
 bill     | Taken | 10/15/2014 6:53 AM

谢谢

Answer 1

使用“分析”函数 row_number()

SELECT
      username
    , event
    , insertdate
FROM (
            SELECT
                  users.username
                , logs.event
                , logs.insertdate
                , ROW_NUMBER() OVER (PARTITION BY users.username
                                     ORDER BY logs.insertdate DESC) AS rn
            FROM users
                  INNER JOIN logs
                              ON users.identifier = logs.useridentifier
            WHERE (logs.event = "Taken"
                  OR logs.event = "Return")
      ) AS derived
WHERE rn < 3
ORDER BY
      username, insertdate DESC;

这个变体可能值得一试，看看它是否有助于性能(或不):

SELECT
      users.username
    , logrn.event
    , logrn.insertdate
FROM users
      INNER JOIN (
                  SELECT
                        logs.useridentifier
                      , logs.event
                      , logs.insertdate
                      , ROW_NUMBER() OVER (PARTITION BY logs.useridentifier
                                           ORDER BY logs.insertdate DESC) AS rn
                  FROM logs
                  WHERE (logs.event = "Taken" OR logs.event = "Return")
            ) AS logrn
                  ON users.identifier = logrn.useridentifier
                        AND rn < 3
ORDER BY
      users.username, logrn.insertdate DESC
;