mysql - 根据最近的日期关联两个表

Question

在我的应用程序中，用户可以登录然后分享一些东西。我想获取他们与分享内容相关的最新登录信息(例如，早于分享日期的 MAX 登录日期)

我有两个表(它们通过 csh.actor = ul.user_id 关联):

CollectionShares (csh)

| id | actor | shareDate

用户登录(ul)

| id | user_id | httpReferer | browser | ipAddress | loginDate

我的一个尝试:

SELECT
    csh.shareDate AS shareDate,
    ul.httpReferer AS httpReferer,
    ul.browser AS browser,
    ul.ipAddress AS ipAddress,
    max(ul.loginDate) AS nearestLastLoginDate
FROM collectionsharehistory csh
JOIN userslogins ul ON 
    csh.actor = ul.user_id 
    AND 
    ul.loginDate <= csh.shareDate
GROUP BY csh.id;

这实际上似乎获得了正确的 ul.loginDate，但其他 ul 列不正确并且似乎与返回的 实际上不匹配登录日期。

Answer 1

您可以使用以下查询:

SELECT ul.id, ul.user_id, ul.ipAddress, ul.loginDate, csh.shareDate                  
FROM UsersLogins AS ul
INNER JOIN CollectionSharesHistory AS csh 
  ON ul.user_id = csh.actor AND ul.loginDate <= shareDate

为了让所有用户登录都有相关的后日期 CollectionSharesHistory 记录。

使用变量，您可以从上述查询生成的派生表中选择所需的记录:

SELECT id, user_id, ipAddress, loginDate, shareDate
FROM (
  SELECT id, user_id, ipAddress, loginDate, shareDate,
         @rn := IF (@user <> user_id,
                   IF (@user := user_id, 1, 1),
                   IF (@user := user_id, @rn + 1, @rn + 1)) AS rn
  FROM ( ... above query here ... ) AS t
  CROSS JOIN (SELECT @user := -1, @rn := -1) AS vars
  ORDER BY user_id, loginDate DESC, shareDate ASC ) AS s
WHERE s.rn = 1

@rn 变量用于枚举每个 user_id 分区内的记录。一旦 user_id 发生变化，@rn 就会被重置，枚举从 1 开始。

ORDER BY 子句将所需的记录放在每个 user_id 切片的顶部。因此，我们可以使用带有 WHERE s.rn = 1 子句的外部查询轻松选择所需的行。

Demo here