如何从mysql查询中的每个组中选择多个项目？ [英] How do I select multiple items from each group in a mysql query?

问题描述

我有一些形式的论坛数据

post（author，thread_id，text）

每个作者，我想选择与该作者相关联的10个不同的thread_id（可能有多于10个，并且编号会因作者而异）。

我正在考虑使用GROUP BY将作者分组，但我无法理解如何在每个组上表达LIMIT，以及如何将每个组展开到10行。

解决方案

以下是每组最多N个类型查询的解决方案。

请注意，您必须为所需的给定作者选择 <10>线程。对于这个例子，我假设你想要最近的线程（并且thread_id是一个自动增量值），并且对于关系的情况，你有一个主键 posts.post_id



  SELECT p1。* 
 FROM post p1 LEFT OUTER JOIN post p2 
 ON（p1 .author = p2.author AND（p1.thread_id< p2.thread_id 
 OR p1.thread_id = p2.thread_id AND p1.post_id< p2.post_id））
 GROUP BY p1.author 
 HAVING COUNT（*）< 10; 
  





---

在评论中重复您的后续问题，这里是解释：

在每个作者的前10个线程中，我们可以说对于其中的每个线程，该作者属于结果的线程数为9或更少组。因此，对于每个作者的帖子（p1），我们计算同一作者有多少帖子（p2）具有更大的帖子。如果该计数小于10，则该作者的帖子（p1）属于该结果。

我添加了一个术语来解决与post_id的关系。

I have some forum data of the form

post(author, thread_id, text)

For each author, I would like to select 10 distinct thread_ids associated with that author (there may be more than 10, and the number will vary by author).

I'm thinking of using GROUP BY to group on 'author', but I cannot understand how to express the LIMIT on each group, and how to expand each group back into 10 rows.

解决方案

Here's a solution to "top N per group" type queries.

Note that you have to choose which 10 threads for a given author you want. For this example, I'm assuming you want the most recent threads (and thread_id is an auto-increment value), and for cases of ties, you have a primary key posts.post_id.

SELECT p1.*
FROM post p1 LEFT OUTER JOIN post p2
 ON (p1.author = p2.author AND (p1.thread_id < p2.thread_id 
   OR p1.thread_id = p2.thread_id AND p1.post_id < p2.post_id))
GROUP BY p1.author
HAVING COUNT(*) < 10;

---

Re your follow-up question in the comment, here's the explanation:

In the top 10 threads per author, we can say that for each of these, there are 9 or fewer other threads for that author belonging to the result set. So for each author's post (p1), we count how many posts (p2) from the same author have a greater thread. If that count is less than 10, then that author's post (p1) belongs in the result.

I added a term to resolve ties with the post_id.

这篇关于如何从mysql查询中的每个组中选择多个项目？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！