SQL用AVG联接两个表

本文介绍了SQL用AVG联接两个表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正试图联接两个表:

songs id | song | artist ---|------|------- 1 | foo | bar 2 | fuu | bor 3 | fyy | bir score id | score ---|------ 1 | 2 2 | 4 3 | 8 2 | 6 3 | 2

使用此SQL命令:

SELECT songs.id, songs.song, songs.artist, score.score FROM songs LEFT JOIN score ON score.id=songs.id ORDER BY songs.id, score DESC

我得到的是同一首歌曲的重复，并具有多个乐谱，我希望将乐谱平均.

What I get back is duplicates of the same song with multiple scores, I would like the score to be averaged.

result id | song | artist | score ---|------|--------|------- 1 | foo | bar | 2 2 | fuu | bor | 4 2 | fuu | bor | 6 3 | fyy | bir | 8 3 | fyy | bir | 2

我尝试使用:

SELECT songs.id, songs.song, songs.artist, ROUND(AVG(score.score),1) AS 'score' FROM songs INNER JOIN score ON score.id=songs.id ORDER BY score DESC

但这平均所有分数，而不仅仅是每首歌曲的分数

But that averages all scores, not just the score of each individual song

result id | song | artist | score ---|------|--------|------- 1 | foo | bar | 4.4

推荐答案

您需要对要保留的所有字段进行GROUP BY:

You need to GROUP BY all the fields you want to retain:

SELECT songs.id, songs.song, songs.artist, AVG(score.score * 1.0) AS AvgScore FROM songs LEFT JOIN score ON score.id=songs.id GROUP BY songs.id, songs.song, songs.artist ORDER BY songs.id, score DESC

或者，您可以执行以下操作:

Alternatively, you could just do this:

SELECT songs.id, songs.song, songs.artist, (SELECT AVG(Score) FROM score WHERE score.id = songs.id) AS AvgScore) FROM songs