选择X组中最大的N条记录

本文介绍了选择X组中最大的N条记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

因此，我一直在按每人最多"标签进行所有问题的解答，或者我不明白自己正在阅读的内容，或者到目前为止没有任何内容可以满足我的需求. 该链接还提供了许多有用的信息，但仍然没有答案.

So I've been running through all the questions under the greatest-n-per-group tag, and either I don't understand what I'm reading, or nothing has fit my needs so far. This link has also provided a lot of useful information, but still no answer.

所以我有一个包含以下字段的表:

So I've got a table with the following fields:

• id(唯一整数)
• user_id(int)
• 类别(varchar)
• 得分(int)
• 兴趣(int)

我认为我的问题与常见的每组最多n个问题相去甚远，因为我不需要每个组中最大的n.我需要X组最多的N条记录.

I believe my problem strays from the common greatest-n-per-group question, in that I don't need the greatest N for every group. I need the greatest N records for X groups.

因此，我需要获得兴趣最高的X个类别(简单，GROUP BY类别ORDER BY兴趣，LIMIT X).然后我需要获得N个记录，其中每个类别的得分最高.

So, I need to get X categories with the highest interest (simple, GROUP BY category ORDER BY interest, LIMIT X). And then I need to get N records with the highest score for each of those categories.

我的查询将如何查找这样的内容?

How would my query look for something like this?

很抱歉，如果这确实是其他每组每人最多一次的问题的重复，我只是不了解它们的工作原理.可以的话，请随时关闭它.

根据@tehshrike的建议，这里有更多信息.

Per @tehshrike's prodding, here's a bit more information.

真正发生的是我的用户在许多不同的categories中都有interest.我想生成原始用户所在的categories中具有最高categories的用户列表.

Really what's happening is that my user's have interest in lots of different categories. I would like to generate a list of users with the highest score in the categories that the original user is interested in.

所以我要寻找的一件事可能是:

So one possible thing that I'm looing for is:

在4个categories中，score最高的15个用户是用户1在interested中的最高位置.

The 15 users with the highest score in the 4 categories that user 1 is most interested in.

对于特定用户ID ，请查找排名前X的类别(对该特定用户最感兴趣)，对于那些(X类别)，请查找排名前N的用户(这些类别得分最高).

For a specific user_id find Top X categories (with highest interest for that specific user) and for those (X categories) find Top N users (with highest score for those categories).

推荐答案

SELECT catX.category catX.interest t1.user_id t1.score FROM ( SELECT category , interest FROM tableX WHERE user_id = @user_id_we_are_interested_in --- specific user ORDER BY interest DESC LIMIT @X --- top @X categories per specific user ) AS catX JOIN tableX AS t1 ON t1.category = catX.category LEFT JOIN tableX AS t2 ON t2.category = t1.category AND t2.score > t1.score GROUP BY t1.category , t1.user_id HAVING COUNT(t2.score) < @N --- top @N users per category ORDER BY catX.interest DESC , t1.score DESC