在 GROUP BY 之后加入?

2021-12-17 00:00:00 join group-by mysql group-concat

我有一张包含歌曲"、Songs_Tags"(与标签相关的歌曲)和Songs_Votes"(与布尔值喜欢/不喜欢的歌曲相关)的表格.

I have a table of "Songs", "Songs_Tags" (relating songs with tags) and "Songs_Votes" (relating songs with boolean like/dislike).

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

I need to retrieve the songs with a GROUP_CONCAT() of its tags and also the number of likes (true) and dislikes (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() 时，它会返回更多结果.

The problem is that when a Song has more than 1 tag, it gets returned more then once, so when I do the COUNT(), it returns more results.

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

The best solution I could think is if it would be possible to do the last LEFT JOIN after the GROUP BY (so now there would be only one entry for each song). Then I'd need another GROUP BY m.id.

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

Is there a way to accomplish that? Do I need to use a subquery?

推荐答案

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

There've been some good answers so far, but I would adopt a slightly different method quite similar to what you described originally

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 个是反对票.

In this the subquery is responsible for collating songs with tags into a 1 row per song basis. This is then joined onto Votes afterwards. I also opted to simply sum up the v.votes column as you have indicated it is 1 or 0 and therefore a SUM(v.votes) will add up 1+1+1+0+0 = 3 out of 5 are upvotes, while SUM(1-v.vote) will sum 0+0+0+1+1 = 2 out of 5 are downvotes.

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

If you had an index on votes with the columns (id_song,vote) then that index would be used for this so it wouldn't even hit the table. Likewise if you had an index on Songs_Tags with (id_song,id_tag) then that table wouldn't be hit by the query.

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

edit added solution using 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

相关文章