MySQL内连接只选择1行

Question

我有一个表(sampleTable)，其中包含以年-月-日表示的网站访问日期、以年-月-日时-分-秒表示的时间戳、用户 ID 和网站访问 ID。我想选择与最近一天发生的第一次访问相对应的访问 ID。到目前为止，我已经尝试过这样做

select userID, MAX(dayAccessed) as latestDay from sampleTable group by userID;

返回最近一天。然后，我在内部联接中使用它来重新联接该查询与正确排序列的原始表。我的问题是，我无法访问两个用户 ID 在最近一天的最早访问 ID:

SELECT sampleTable.dayTimeAccessed, sampleTable.userID, latest.latestDay 
FROM 
    (SELECT userID, MAX(dayTimeAccessed) AS latestDay 
    from sampleTable 
    group by userID) AS latest 
INNER JOIN sampleTable 
ON sampleTable.userID = latest.userID AND 
    sampleTable.dayTimeAccessed 
limit 1;

仅返回与第一个用户 ID 匹配的字段。

Answer 1

您需要先找到 MAX 日期，然后找到 MIN 时间戳:

SELECT 
   min(sampleTable.dayTimeAccessed) -- min timestamp per user for the MAX date
  ,latest.userID
  ,latest.latestDay 
FROM 
  ( SELECT userID, MAX(dayAccessed) AS latestDay  -- max date per user
    from sampleTable 
    group by userID
  ) AS latest 
INNER JOIN sampleTable 
ON sampleTable.userID = latest.userID
AND sampleTable.dayAccessed = latest.latestDay
GROUP BY latest.userID, latest.latestDay

对于 MySQL 8，您可以使用 ROW_NUMBER:

SELECT *
FROM
 (
   SELECT *, 
      ROW_NUBMER() -- min timestamp per user for the MAX date
      OVER (PARTITION BY userID
            ORDER BY dayAccessed DESC, dayTimeAccessed ASC) AS rn
   FROM sampleTable
 ) t
WHERE rn = 1;