mysql group by join_mysql – GROUP BY之后的LEFT JOIN？

到目前为止已经有了一些很好的答案,但我会采用一种与你原先描述的方法非常相似的方法

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