到目前为止已经有了一些很好的答案,但我会采用一种与你原先描述的方法非常相似的方法
SELECT
songsWithTags.*,
COALESCE(SUM(v.vote),0) AS votesUp,
COALESCE(SUM(1-v.vote),0) AS votesDown
FROM (
SELECT
s.*,
COLLATE(GROUP_CONCAT(st.id_tag),'') AS tags_ids
FROM Songs s
LEFT JOIN Songs_Tags st
ON st.id_song = s.id
GROUP BY s.id
) AS songsWithTags
LEFT JOIN Votes v
ON songsWithTags.id = v.id_song
GROUP BY songsWithTags.id DESC
在此,子查询负责将带有标签的歌曲整理为每首歌曲1行.然后将其加入投票.我还选择简单地总结v.votes列,因为你已经指出它是1或0因此SUM(v.votes)将加起来1 1 1 0 0 = 3中有3是upvotes,而SUM(1 -v.vote)将总和0 0 0 1 1 = 5中的2个是downvotes.
如果你有一个带有列(id_song,vote)的投票索引,那么该索引将用于此,所以它甚至不会击中表.同样,如果您在Songs_Tags上有一个索引(id_song,id_tag),那么该表将不会被查询命中.
使用count编辑添加的解决方案
SELECT
songsWithTags.*,
COUNT(CASE WHEN v.vote=1 THEN 1 END) as votesUp,
COUNT(CASE WHEN v.vote=0 THEN 1 END) as votesDown
FROM (
SELECT
s.*,
COLLATE(GROUP_CONCAT(st.id_tag),'') AS tags_ids
FROM Songs s
LEFT JOIN Songs_Tags st
ON st.id_song = s.id
GROUP BY s.id
) AS songsWithTags
LEFT JOIN Votes v
ON songsWithTags.id = v.id_song
GROUP BY songsWithTags.id DESC