按平均值优化sql结果集

I have the following line of SQL that returns all fanart in the db that has a rating of 10... currently it works, but not as initially intended...

我有以下SQL行返回数据库中所有评级为10的fanart ...目前它可以工作,但不是最初预期的...

What I'd like is for it to return results where the average rating of a fanart is 10... each fanart row in the rating table can refer to the same itemid, allowing us to have multiple user ratings per single peice of artwork... i'd like it only return distinct itemid's where the average of all ratings is 10.

我想要的是返回结果,其中fanart的平均评级为10 ...评级表中的每个fanart行可以引用相同的itemid,允许我们每个艺术品的多个用户评级。 ..我希望它只返回不同的itemid,其中所有评级的平均值为10。

SELECT DISTINCT g.gametitle, 
                p.name, 
                g.id, 
                b.filename 
FROM   games AS g, 
       banners AS b, 
       platforms AS p, 
       ratings AS r 
WHERE  r.itemid = b.id 
       AND r.rating = '10' 
       AND g.id = b.keyvalue 
       AND r.itemtype = 'banner' 
       AND b.keytype = 'fanart' 
       AND g.platform = p.id 
ORDER  BY Rand() 
LIMIT  6;

Here's hoping that someone smarter than me can figure this out!

这里希望比我聪明的人能想出来!

I tried creating a sub-query that used the AVG() function but to no avail.

我尝试创建一个使用AVG()函数的子查询但无济于事。

Looking forwards to some thought provoking replies!

期待一些发人深省的回复!

1 个解决方案

#1

This calls for AGGREGATION!

这需要AGGREGATION!

   SELECT g.gametitle, 
          p.name, 
          g.id, 
          b.filename 
   FROM   games AS g, 
          banners AS b, 
          platforms AS p, 
          ratings AS r 
   WHERE  r.itemid = b.id 
          AND g.id = b.keyvalue 
          AND r.itemtype = 'banner' 
          AND b.keytype = 'fanart' 
          AND g.platform = p.id 
   GROUP BY g.gametitle, 
            p.name, 
            g.id, 
            b.filename 
   HAVING AVG(r.rating) = 10

1 个解决方案

#1

更多相关文章

随机推荐