mysql - 每周最大平均收视率

Question

我有图像表，每个图像都得到评级

SELECT imagesrc,AVG (rating / total_votes) AS maximumrate FROM imagerate GROUP BY ImageSN order by AVG( rating/total_votes ) DESC  LIMIT 1 

我得到了最大平均评级图像，但我想要一周的最大评级图像，因为我搜索并发现下面的代码接近我的要求，但我无法将它们放在一起工作

WHERE time BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)

imagerate structure is 

ImageSN (primary key)
imagesrc (imagepath)
time (uploaded at (date)) 
rating
total votes

需要专家意见帮助!

修改

$requestthree = 'SELECT imagesrc,AVG (rating / total_votes) AS maximumrate FROM imagerate  WHERE time >= curdate() - INTERVAL DAYOFWEEK(curdate())+7 DAY
AND time < curdate()  - INTERVAL DAYOFWEEK(curdate())-1 DAY  GROUP BY ImageSN order by AVG( rating/total_votes )DESC LIMIT 1';

现在查询给了我每周最高评级，但是如果我的数据库有两个具有高平均评级的图像，我想知道哪些图像获得更多选票

我尝试添加

GROUP BY total_votes as MAX() 将使此查询无效

Answer 1

上周尝试添加以下内容:

SELECT imagesrc,AVG (rating / total_votes) AS maximumrate 
FROM imagerate
WHERE time >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND time < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
GROUP BY ImageSN order by AVG( rating/total_votes ) DESC  LIMIT 1;