mysql - 寻找一种方法来连接有限数量的行

Question

我对我想要运行的查询提出了以下理论解决方案:

SELECT m.id, u.user_id, COUNT(u.user_id) FROM matches m
JOIN (
    SELECT * FROM match_user u1 WHERE user_id IN (
        SELECT user_id FROM match_user WHERE match_id = u1.match_id ORDER BY created_at LIMIT 10
    )
) u ON u.match_id = m.id
GROUP BY u.user_id 
ORDER BY COUNT(u.user_id)

然而，正如它告诉我的那样，MySQL“还不支持‘LIMIT & IN/ALL/ANY/SOME 子查询’”。

我希望仅将 match_user 中最旧的 10 个条目加入到 matches 中。如何实现这一目标？

转储带有虚拟数据的表:http://ge.tt/20zOdpL1/v/0

编辑

根据 Strawberry 的建议，我尝试了 SQL Fiddle 并放入了我正在使用的虚拟数据:http://sqlfiddle.com/#!2/7a3e9

所需的查询输出包含两列:user_id、number_of_matches

number_of_matches 是 user_id 通过 match_user 表与匹配项关联的次数。

到目前为止一切顺利:

SELECT u.user_id, COUNT(u.user_id)
FROM matches m
JOIN match_user u ON u.match_id = m.id
GROUP BY u.user_id 

问题是每场比赛只允许 10 名玩家排队。我们将对粘贴到 SQL Fiddle 中的数据使用 3 的限制，因为没有足够的虚拟数据容纳 10 个。

因此，我想限制连接表，以确保对于每个匹配项，我们仅获取 match_user 中最旧的 3 个条目。

这意味着最先回复 (created_at) 的 3 名玩家 (user_id) 正在玩游戏。

引用 SQL Fiddle，应该省略 match_user 中 id=5 的行，因为这是唯一有超过 3 个用户 RSVP 的情况匹配，这是 match_id=2

的最早回复

进一步限制

如果我们将每场比赛限制为 2 名玩家，则输出中还会排除另外两行。 id=4 行和 id=7 行，因为它们是 match_id 中最早的行。

Answer 1

我假设您想要匹配列表(如您的评论中所示)，而不是原始问题中建议的按 user_id 进行某种计数。无论如何，如果您确实想要的话，您可以将聚合添加到以下查询中。

此查询使用 where 子句为每个 matchid 获取最多 10 个用户 ID。这个想法是计算 match_id 较小的用户数量，并将该数字与 10 进行比较:

select mu.match_id, mu.user_id
from match_user mu join
     matches m
     on mu.match_id = m.id
where 10 >= (select count(*)
             from match_user mu2 join
                  matches m2
                  on mu2.match_id = m2.id
             where mu2.match_id = mu.match_id and
                   mu2.created_at <= mu.created_at
            );

编辑:

以下查询按用户进行摘要:

select mu.user_id, count(*)
from match_user mu join
     matches m
     on mu.match_id = m.id
where 10 >= (select count(*)
             from match_user mu2 join
                  matches m2
                  on mu2.match_id = m2.id
             where mu2.match_id = mu.match_id and
                   mu2.created_at <= mu.created_at
            )
group by mu.user_id;

SQL fiddle 是 here .

编辑二:

这是如何工作的？显然，查询中有趣的部分是 where 子句中的子查询。此子查询计算 created_at 日期小于外部查询中给定记录的 match_user 记录的数量。您可以将其视为一个“循环”，尽管实现不一定是嵌套循环。

子查询中的where子句是子查询的关键部分。第一个子句查看具有相同 match_id 的所有记录。第二部分仅匹配较早的 created_at 记录。 count(*) 计算数量，外部过滤器仅在最多 10 条记录时保留特定记录。

当我查看查询时，我意识到子查询中的联接是不必要的，外部查询中也是如此。这应该会产生相同的结果:

select mu.user_id, count(*)
from match_user mu
where 10 >= (select count(*)
             from match_user mu2
             where mu2.match_id = mu.match_id and
                   mu2.created_at <= mu.created_at
            )
group by mu.user_id;