SQL用AVG联接两个表 [英] SQL JOIN two tables with 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
这篇关于SQL用AVG联接两个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文