mysql - GROUP BY 后左加入？

Question

我有一张“Songs”、“Songs_Tags”(将歌曲与标签相关联)和“Songs_Votes”(将歌曲与 bool 喜欢/不喜欢相关联)的表格。

我需要检索带有标签的 GROUP_CONCAT() 的歌曲以及喜欢的次数 (true) 和不喜欢的次数 (false)。

我的查询是这样的:

SELECT
    s.*,
    GROUP_CONCAT(st.id_tag) AS tags_ids,
    COUNT(CASE WHEN v.vote=1 THEN 1 ELSE NULL END) as votesUp,
    COUNT(CASE WHEN v.vote=0 THEN 1 ELSE NULL END) as votesDown,
FROM Songs s
    LEFT JOIN Songs_Tags st ON (s.id = st.id_song)
    LEFT JOIN Votes v ON (s.id=v.id_song)
GROUP BY s.id
ORDER BY id DESC

问题是，当一首歌曲有超过 1 个标签时，它会返回不止一次，所以当我执行 COUNT() 时，它会返回更多结果。

我能想到的最佳解决方案是是否可以在 GROUP BY 之后执行最后一个 LEFT JOIN(因此现在每首歌曲只有一个条目)。然后我需要另一个 GROUP BY m.id。

有没有办法做到这一点？我需要使用子查询吗？

Answer 1

到目前为止已经有一些很好的答案，但我会采用与您最初描述的方法非常相似的稍微不同的方法

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 = 5 个中有 3 个是赞成票，而 SUM(1-v.vote) 将求和 0+0+0+1+1 = 5 个中有 2 个是反对票。

如果您有一个包含列 (id_song,vote) 的投票索引，那么该索引将用于此目的，因此它甚至不会命中表。同样，如果您在 Songs_Tags 上有一个带有 (id_song,id_tag) 的索引，那么该表将不会被查询命中。

编辑使用计数添加的解决方案

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