MySQL最大N结果与联接表 [英] MySQL Greatest N Results with Join Tables
问题描述
选择前n个结果，我在这里看到了很多关于如何执行此操作的文章和精彩文章，但是我正努力用自己的数据集来完成它.大多数示例着重于数据集，而无需其他联接.
Selecting the Top n Results, I've seen the numerous posts and great articles on here about how to do it but I am struggling to do it with my data set. Most of the examples focus on data sets without the need for additional joins.
I've been trying to apply the examples from http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ to my query without much success.
个人，信用和媒体这三个表存在.
Three tables exist Person, Credit and Media.
人员链接到信用"和信用到媒体".
Person links to Credit and Credit to Media.
以下查询应返回人均排名前5位的媒体，但不是，我哪里出错了?
The query below should return the top 5 medias per person, however it doesn't, where have I gone wrong?
SELECT
p.id AS person_id,
c.id AS credit_id,
m.id AS media_id, m.rating_average
FROM person p
INNER JOIN credit c ON c.person_id = p.id
INNER JOIN media m ON m.id = c.media_id
where (
select count(*) from media as m2
inner JOIN credit c2 on m2.id=c2.media_id
where c2.person_id = c.person_id and m2.rating_average >= m.rating_average
) <= 5
说明:
热门媒体是根据具有最高rating_average平均值的媒体计算得出的.
Top Medias are calculated from those with the highest rating_average.
更新:
SQLFiddle http://sqlfiddle.com/#!9/eb0fd
SQLFiddle http://sqlfiddle.com/#!9/eb0fd
每人(p)所需的前3种媒体(m)的期望输出.显然，我希望能够在前5种媒体上做到这一点，但这只是测试数据.
Desired output for top 3 medias (m) per person (p). Obviously I would like to do be able this for the top 5 medias but this is only test data.
p m c rating_average
1 9 27 9
1 7 28 8
1 1 1 8
2 1 5 8
2 4 8 8
2 7 29 8
3 4 10 8
3 3 9 6
3 5 11 5
4 3 13 6
4 5 14 5
4 6 15 3
5 4 16 8
5 5 17 5
5 6 18 3
6 6 19 3
7 7 20 8
8 9 23 9
8 1 21 8
8 8 22 0
9 1 24 8
9 7 26 8
9 5 25 5
推荐答案
我认为我可以解决它:)
i think i solve it :)
首先，这是一个根据您的开始方式提供的解决方案.但是有一个问题，我无法解决，不能显示每个person_id的确切3行(或您选择的任何数字，例如我选择3).问题在于解决方案是基于计算有多少行，而rating_average大于当前行.因此，如果您有5个相同的最高价值，则可以选择全部显示5个，也可以不显示全部5个，这不好.因此，这就是您的操作方式...(当然，这是示例，如果您拥有4个最高价值，则将它们全部显示出来(我认为不显示数据根本没有意义))...
First here is one solution based on the way you started. But there is a catch I couldn't solve it to show exact 3 (or whatever number you choose i pick 3 for example) row for each person_id. Problem is that solution is based on counting how many rows is there with the rating_average greater then current row. So if you have 5 same top value you could choose to show all 5 or not to show them at all and that's not good. So here is the way you do that... (of course this is example where if you have 4 top value you show them all (I think that no make sense at all to not show the data))...
SELECT t1.person_id, t1.credit_id, t1.media_id, t1.rating_average
FROM (SELECT p.id AS person_id, c.id AS credit_id, m.id AS media_id,
m.rating_average AS rating_average
FROM person p
INNER JOIN credit c ON c.person_id = p.id
INNER JOIN media m ON m.id = c.media_id) as t1
WHERE (SELECT COUNT(*)
FROM (SELECT p.id AS person_id, c.id AS credit_id, m.id AS media_id,
m.rating_average AS rating_average
FROM person p
INNER JOIN credit c ON c.person_id = p.id
INNER JOIN media m ON m.id = c.media_id) AS t2
WHERE t2.person_id = t1.person_id AND t2.rating_average > t1.rating_average) < 3
ORDER BY person_id ASC, rating_average DESC
重要提示::如果您没有自我重复的价值，则此解决方案可以起作用(为每个人显示准确的3行)...这是小提琴手
Important: This solution can work (to show exact 3 rows for each person) if you don't have value that repeat it self... Here is the Fiddle http://sqlfiddle.com/#!9/eb0fd/64 you can see the problem where person_id is 1!
在那之后，我演奏了更多的曲目，并使其按照您认为的问题运行.这是一个代码:
After that i played a little more and make it work just as you wanted in the question i think. Here is a code for that:
SET @num := 0, @person := 0;
SELECT person_id, credit_id, media_id, rating_average, rowNumber
FROM (SELECT t1.person_id, t1.credit_id, t1.media_id, t1.rating_average,
@num := if(@person = t1.person_id, @num + 1, 1) AS rowNumber,
@person := t1.person_id
FROM (SELECT p.id AS person_id, c.id AS credit_id, m.id AS media_id,
m.rating_average AS rating_average
FROM person p
INNER JOIN credit c ON c.person_id = p.id
INNER JOIN media m ON m.id = c.media_id
ORDER BY p.id ASC, m.rating_average DESC) as t1) as t2
WHERE rowNumber <= 3
这是 http://sqlfiddle.com/#!9/eb0fd/65 ...
GL！
P. S.对不起，我的英语希望您能理解我在说什么...
P. S. sorry for my English hope you could understand what i was talking about...
这篇关于MySQL最大N结果与联接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
