GROUP BY后加入左边？ [英] LEFT JOIN after GROUP BY?

问题描述

我有一张歌曲，歌曲_标签（与标签相关的歌曲）和歌曲_Votes（与布尔喜欢/不喜欢的歌曲相关）。

我需要检索带有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）作为votesDown，
 FROM Songs s 
 LEFT JOIN Songs_Tags st ON（s.id = st.id_song）
 LEFT JOIN votes（s.id = v.id_song）
 GROUP BY s.id 
 ORDER BY id DESC 
  

问题在于，当一首歌曲超过1个标签时，它会多次返回，所以当我做COUNT（）时，它会返回更多的结果。

我能想到的最佳解决方案是，如果可以在G之后执行最后一个LEFT JOIN ROUP BY（现在每首歌曲只有一个条目）。然后我需要另一个GROUP BY m.id。

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

解决方案

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

pre $ 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歌曲
LEFT JOIN Songs_Tags st
ON st.id_song = s.id
GROUP BY s。 id
）AS songsWithTags
LEFT JOIN票数v
ON songsWithTags.id = v.id_song

GROUP BY songsWithTags.id DESC

在这个子查询中，负责将带标签的歌曲按每首歌曲排成一行。然后这些会被加入到投票中。我还选择简单地总结v.votes列，因为您已经指出它是1或0，因此SUM（v.votes）将加起来1 + 1 + 1 + 0 + 0 = 3是upvotes中的5个，而SUM（1-v.vote）将总结0 + 0 + 0 + 1 + 1 = 2中的5个是downvotes。

如果你有一个带有列（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歌曲
 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 
  

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

I need to retrieve the songs with a GROUP_CONCAT() of its tags and also the number of likes (true) and dislikes (false).

My query is something like that:

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

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.

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

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.

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

这篇关于GROUP BY后加入左边？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！