Question

我有以下几条卡片线,它把10分级的 d鱼全部归来。它目前运作,但并非最初意图。

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.

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;

在这里,希望有人比我这样说!

我尝试建立一个分局,利用AVG(AVG)的功能,但没有结果。

期待一些有想法的答复!

Answer 1

这项呼吁要求<> 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

友情链接