MySQL:根据第二个表中真假的比率选择行

Question

我有一个媒体文件表和第二个媒体文件评级表。

我应该使用什么 SQL 语句从第二个表中正负收视率最高的第一个表中选择媒体文件？

下表包含与每个媒体文件相关的信息。

表："媒体"

| mediaID |
-----------
|       3 |
|      22 |

鉴于下表;媒体文件 #3 的评级为 2/3 或 66%，#22 的评级为 1/2 或 50%。

表："评级"

| mediaID | rating |
--------------------
|       3 |      1 |
|       3 |      1 |
|       3 |      0 |
|      22 |      1 |
|      22 |      0 |

任何帮助将不胜感激:)我已经做到了：

SELECT media.mediaID, (
    (SELECT COUNT(CASE WHEN rating =1 THEN 1 END ) Positive FROM ratings)
        /
    (SELECT COUNT( mediaID ) FROM ratings )
) AS percent
FROM ratings, media
WHERE media.mediaID = ratings.mediaID
GROUP BY mediaID

此外，我突然想到两个或多个媒体文件之间的赞成票与总票数的比例可能存在平局。在这种情况下，我怎么能让 MySQL 只选择一个？

Answer 1

试试这个

    select r.mediaid, 
       count(*) as total_rows, 
       sum(rating) as id_sum,
       SUM(rating)/count(*) AS score
    from rating r, media m
    where r.mediaid=m.mediaid
    group by r.mediaid

如果只想报告分数高于阈值（如 0.75）的记录然后添加"拥有"子句

 select r.mediaid, 
        count(*) as total_rows, 
        sum(rating) as id_sum,
        SUM(rating)/count(*) AS score
   from rating r, media m
  where r.mediaid=m.mediaid
  group by r.mediaid
  having score > .75  

这是一个演示 SQL 小提琴

评论后

一种方法是按分数 desc 排序，然后限制为 1 条记录，如下所示 SQL Fiddle#2

    select r.mediaid, 
     count(*) as total_rows, 
     sum(rating) as id_sum,
     SUM(rating)/count(*) AS score
from rating r, media m
 where r.mediaid=m.mediaid
 group by r.mediaid
order by score desc limit 1