MySQL 查询，尝试收集计数

Question

我正在尝试编写一个查询来获取数据库中每个唱片公司的艺术家和乐队的数量。由于某种原因，当我运行以下查询时，它没有列出分配给数据库中乐队或艺术家的每个记录标签。

 SELECT  r.labelName, 
        COUNT(a.recordLabelID) +COUNT(b.recordLabelID) AS count 
        FROM recordLabel r JOIN artist a ON r.recordLabelID = a.recordLabelID
        JOIN band b ON r.recordLabelID = b.recordLabelID
        GROUP BY r.recordlabelID ORDER BY count DESC

Answer 1

尝试使用左连接:

SELECT r.labelName, COUNT(a.recordLabelID) + COUNT(b.recordLabelID) AS count
FROM recordLabel r JOIN
     artist a
     ON r.recordLabelID = a.recordLabelID JOIN
     band b
     ON r.recordLabelID = b.recordLabelID
GROUP BY r.recordlabelID
ORDER BY count DESC;

但是，这不起作用，因为计数将返回相同的值。一个简单的解决方法是使用 COUNT(DISTINCT):

SELECT r.labelName,
       COUNT(DISTINCT a.recordLabelID) + COUNT(DISTINCT b.recordLabelID) AS count
FROM recordLabel r LEFT JOIN
     artist a
     ON r.recordLabelID = a.recordLabelID LEFT JOIN
     band b
     ON r.recordLabelID = b.recordLabelID
GROUP BY r.recordlabelID
ORDER BY count DESC;

您需要使用LEFT JOIN，以防某些唱片公司有艺术家但没有乐队，反之亦然。

更具可扩展性的解决方案是在进行连接之前进行聚合。