mysql - Left Join with Count 和右侧的 Where 子句

Question

我有以下查询:

SELECT G.groupCode, G.groupName, COUNT(C.cdCode) AS numberOfTops10CDs FROM musicalgroup G 
    LEFT OUTER JOIN cd C ON C.groupCode = G.groupCode
    WHERE C.cdCode IN 
        (SELECT cdCode FROM topcds WHERE rating <= 10)
GROUP BY G.groupCode

在这种情况下，如果关联的计数为 0，它不会显示组。我希望它显示 musicgroup 中的每个元素，即使计数为 0。当我这样做时:

 SELECT G.groupCode, G.groupName, COUNT(C.cdCode) AS numberOfTops10CDs FROM musicalgroup G 
     LEFT OUTER JOIN cd C ON C.groupCode = G.groupCode
     GROUP BY G.groupCode

即使计数为 0，它也会显示，但我只需要显示评分低于 10 的 CD。我如何在一个查询中实现这两个目标？

Answer 1

试试这个:

SELECT G.groupCode,
       G.groupName,
       COUNT(C.cdCode) AS numberOfTops10CDs 
FROM musicalgroup G 
LEFT OUTER JOIN cd C
    ON C.groupCode = G.groupCode AND
       C.cdCode in (SELECT distinct cdCode FROM topcds WHERE rating <= 10)
GROUP BY G.groupCode,G.groupName