带有联接条件的LIMITing a SQL联接

Question

我遇到了一个类似于LIMITing a SQL JOIN的问题，但有一个稍微复杂的需求。

我想搜索用户和相关的事务，它们在一个时间范围内：

SELECT u.*, t.*
FROM User u
JOIN Transaction t ON t.user_id = u.id
WHERE t.timestamp >= ? and t.timestamp <= ?;

到现在为止还好。现在我想重复这个查询，但是返回的用户数量是有限的。但是，对于给定用户返回的事务数量不应该有限制。

如果我按照另一个问题中所建议的办法，这将转化为：

SELECT u.*, t.*
FROM (SELECT * FROM User LIMIT 10) u
JOIN Transaction t ON t.user_id = u.id
WHERE t.timestamp >= ? and t.timestamp <= ?;

这不会产生我想要的结果:它将返回前10个用户，他们可能没有任何相关的事务。

我希望返回10个在给定时间范围内具有至少一个关联事务的用户。

如何使用MySQL实现这一目标？

Answer 1

您可以为此使用变量：

SELECT *
FROM (
  SELECT *,
         @rn := IF(@uid = user_id, @rn,
                   IF(@uid := user_id, @rn +1, @rn + 1)) AS rn 
  FROM (
     SELECT u.*, t.*
     FROM User u
     JOIN Transaction t ON t.user_id = u.id
     WHERE t.timestamp >= x and t.timestamp <= y) AS t
  CROSS JOIN (SELECT @rn := 0, @uid := 0) AS vars
  ORDER BY user_id) AS x
WHERE x.rn <= 10

每次查询返回新用户时，变量@rn都会增加1。因此，我们可以使用@rn <= 10控制返回的用户数。

Answer 2

您可以在没有变量的情况下这样做，但是它需要重复join逻辑：

SELECT u.*, t.*
FROM (SELECT *
      FROM User
      WHERE EXISTS (SELECT 1
                    FROM Transaction t
                    WHERE t.user_id = u.id AND
                          t.timestamp >= ? and t.timestamp <= ?
                   )
      LIMIT 10
     ) u JOIN
     Transaction t
     ON t.user_id = u.id
WHERE t.timestamp >= ? and t.timestamp <= ?;

编辑：

也许最快的答案是这样的：

select u.*, t.*
from (select user_id
      from (select user_id
            from transaction t
            where t.timestamp >= ? and t.timestamp <= ?
            limit 1000
           ) t
      limit 30
     ) tt join
     user u
     on tt.userid = u.id join
     transaction t
     on tt.userid = t.userid and t.timestamp >= ? and t.timestamp <= ?;

第一个子查询在事务表中选择1,000个匹配记录。我的猜测是，这足以得到30多个用户。然后将该列表连接到用户和事务表，以获得最终结果。通过限制列表而不必进行完整的表扫描，第一个查询应该非常快。。。特别是在(timestamp, user)上增加了一个索引。