MySQL 中是否有一个查询允许类似于此的可变组号和限制
我已经检查了一些 stackoverflow 问题，也有类似的问题，但没有完全解决这个问题。
如果你有一个这样的表：
uid cat_uid itm_uid
1 1 4
2 1 5
3 2 6
4 2 7
5 3 8
6 3 9
其中 auto_incremented 中的 uid 列和 cat_uid 引用 与过滤相关的类别，并且 itm_uid 值是一个 我们正在寻找
我想要一个包含以下示例结果的结果集：
array (
0 => array (1 => array(4,5)),
1 => array (2 => array(6,7)),
2 => array (3 => array(8,9))
)
一个示例问题是 - 从每个类别中选择 2 条记录（无论类别有多少），并确保它们是 uid 中的最后 2 个条目这些类别。
我不确定如何构建问题以允许答案，并且欢迎任何有关解决方案方法的提示！
编辑： 这不是一个非常明确的问题，所以让我将这个场景扩展到更具体的东西。
我有一组记录正在输入类别，我想通过尽可能少的查询来选择每个类别输入的最新 2 条记录，这样当我列出这些类别的内容时，我将至少有 2 条记录每个类别的记录（假设数据库中已有 2 个或更多）。有一个类似的查询，选择最后 100 条记录并将它们过滤到类别中，但对于少量类别，其中某些类别更新速度比其他类别快，可能会导致前 100 条记录不包含每个类别的成员，因此尝试为了解决这个问题，我一直在寻找一种方法，从每个类别中选择 2 条记录（或者假设每个类别相同，则选择 N 条记录），并将这 2 条记录作为最后输入的记录。日期字段可用于排序，但 itm_uid 本身可用于指示插入顺序。
I've checked out a few of the stackoverflow questions and there are similar questions, but didn't quite put my fingers on this one.
If you have a table like this:
uid cat_uid itm_uid
1 1 4
2 1 5
3 2 6
4 2 7
5 3 8
6 3 9
where the uid column in auto_incremented and the cat_uid references a
category of relevance to filter on and the itm_uid values are the one
we're seeking
I would like to get a result set that contains the following sample results:
array (
0 => array (1 => array(4,5)),
1 => array (2 => array(6,7)),
2 => array (3 => array(8,9))
)
An example issue is - select 2 records from each category (however many categories there may be) and make sure they are the last 2 entries by uid in those categories.
I'm not sure how to structure the question to allow an answer, and any hints on a method for the solution would be welcome!
EDIT:
This wasn't a very clear question, so let me extend the scenario to something more tangible.
I have a set of records being entered into categories and I would like to select, with as few queries as possible, the latest 2 records entered per category, so that when I list out the contents of those categories, I will have at least 2 records per category (assuming that there are 2 or more already in the database). A similar query was in place that selected the last 100 records and filtered them into categories, but for small numbers of categories with some being updated faster than others can lead to having the top 100 not consisting of members from every category, so to try to resolve that, I was looking for a way to select 2 records from each category (or N-records assuming it's the same per-category) and for those 2 records to be the last entered. A date field is available to sort on, but the itm_uid itself could be used to indicate inserted order.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
结果中将有两列额外的列，只需忽略它们即可。
逻辑是这样的：
我们按 cat_uid、uid 降序对表进行排序，然后从顶部开始，为每一行指定一个“行号”(cat_row)，每当 cat_uid 更改时，我们都会将此行号重置为零：
现在，如果我们只保留具有
cat_row < 的行2我们得到了我们想要的结果：You will have two extra columns in the results, just ignore them.
This is the logic:
We sort the table by cat_uid, uid descending, then we start from the top and give each row a "row number" (cat_row) we reset this row number to zero whenever cat_uid changes:
now if we keep only the rows that have
cat_row < 2we get the results we want:这称为相邻树模型或父子树模型。它是最简单的树模型之一，只有 1 个指针或 1 个叶子。您可以通过递归或使用自联接来解决查询。遗憾的是 MySQL 不支持递归查询，也许它正在使用准备好的语句。我想建议您自助加入。通过自联接，您可以获取具有特殊条件的右侧和左侧的所有行。
This is called an adjacent tree model or a parent-child tree model. It's one of the simplier tree model where there is only 1 pointer or 1 leaf. You would solve your query with a recursion or using a Self Join. Sadly MySQL doesn't support recursive queries, maybe it's working with prepared statements. I want to suggest you an Self Join. With a Self Join you can get all the rows from the right side and the left side with a special condition.