仅返回联接表中行的有序子集 [英] Only return an ordered subset of the rows from a joined table

问题描述

在MySQL数据库中给出这样的结构

Given a structure like this in a MySQL database

#data_table
(id) | user_id | time | (...)

#relations_table
(id) | user_id | user_coach_id | (...)

我们可以通过以下方式选择属于某个user_coach_id(假设1)的所有data_table行:

we can select all data_table rows belonging to a certain user_coach_id (let's say 1) with

SELECT rel.`user_coach_id`, dat.*
FROM `relations_table` rel
LEFT JOIN `data_table` dat ON rel.`uid` = dat.`uid`
WHERE rel.`user_coach_id` = 1
ORDER BY val.`time` DESC

返回类似的东西

| user_coach_id | id | user_id | time | data1 | data2 | ...
|             1 |  9 |       4 |   15 |   foo |   bar | ...
|             1 |  7 |       3 |   12 |   oof |   rab | ...
|             1 |  6 |       4 |   11 |   ofo |   abr | ...
|             1 |  4 |       4 |    5 |   foo |   bra | ...

(依此类推.时间实际上并不是整数，而是保持简单.)

但是现在我想(理想情况下)从每个唯一的user_id的data_table中最多查询任意数量的行，但是仍然要对它们进行排序(即最新的).那有可能吗?

But now I would like to query (ideally) only up to an arbitrary number of rows from data_table per distinct user_id but still have those ordered (i.e. newest first). Is that even possible?

我知道我可以使用GROUP BY user_id每个用户只返回1行，但是排序不起作用，结果中哪一行似乎是不可预测的.我猜它对子查询是可行的，但我还没有弄清楚.

I know I can use GROUP BY user_id to only return 1 row per user, but then the ordering doesn't work and it seems kind of unpredictable which row will be in the result. I guess it's doable with a subquery, but I haven't figured it out yet.

推荐答案

限制每个GROUP中的行数很复杂.最好使用@variable进行计数，再加上外部查询以将超出限制的行扔掉.

To limit the number of rows in each GROUP is complicated. It is probably best done with an @variable to count, plus an outer query to throw out the rows beyond the limit.

我在 Groupwise Max 上的博客提供了一些提示怎么做.

My blog on Groupwise Max gives some hints of how to do such.

这篇关于仅返回联接表中行的有序子集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！