MySQL -获取每行的平均值

Question

假设我有3个MySQL表。名为songs的歌曲db包含歌曲的艺术家、专辑等。这里的主要内容是lastfm_id，它是歌曲的唯一id。

然后是一个list (有10个点，比如前10名的歌曲)。人们可以通过将歌曲添加到他们的列表中来创建列表，并将它们排序为1到10。这个数据库并不需要为此进行任何修改。

最后，我们有song_listed，它是人们选择放入列表中的歌曲。下面是：

​

​

如您所见，song_listed中的song_id与表songs中的lastfm_id相同。现在，由于数据库中只有一个列表，因此有10首歌曲。如果创建了3个列表，那么在song_listed中就会有30行，每行都有自己的排名1-10。

如何获得列表中每首歌曲在song_listed中的平均排名？

想象一下，有一个页面通过list.php?id=11912或类似的方式显示列表。然后，您将从song_listed获取歌曲，而list_id是11912。

这是我到目前为止所得到的：

$listSongs = 0;
while ($listSongs <= 9) {

    // Don't worry, I did a query and $songID[x] works.
    $songID = $songID[$listSongs]; // ex: 12949331
    $getAvgRank = mysql_query("") or die(mysql_error());

    }

Answer 1

SELECT song_id, AVG(rank)
FROM song_listed
WHERE song_id in 
  (SELECT s1.song_id FROM song_listed s1 WHERE s1.list_id = <the id of your list_id>)
GROUP BY song_id

编辑

如果你想要一个来自song_id的请求(这不是一个好主意，但是)

SELECT AVG(rank)
FROM song_listed
WHERE song_id = $songID[$realArray]

注意:这只是“合乎逻辑的”解决方案。使用参数化查询(并阅读Ben对mysql_*函数的评论)。

Answer 2

我不认为平均排名给了你正确的信息-假设一个列表中有2首歌曲-歌曲A出现在2个列表中，每次排名1，，歌曲B在200个列表中，每次排名2 =>歌曲A平均排名1，歌曲B平均排名2，即使它更受欢迎

我建议你使用一个“人气”指标来代替(比如排名1得10分，排名10得1分)：

select s.song_id, sum(11 - s.rank) popularity_points
from song_listed s
join song_listed l on s.song_id = l.song_id and l.list_id = :my_list_id
group by s.song_id