mysql - 带有Count的左连接和右侧的Where子句

我有以下疑问：

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，则不显示组我想让它显示musicalgroup中的每个元素，即使计数为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。如何在一个查询中实现这两个目标？

最佳答案

试试这个：

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

关于mysql - 带有Count的左连接和右侧的Where子句，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/39783488/