MySQL 使用 COUNT 扩展 SELECT 查询

Question

我需要一些帮助来创建 MySQL 查询。假设我们有一张“动物”表，其中包含物种以及动物的详细品种。此外，我们还有一张“检查”表，其中包含对动物的调查。要找出有多少只特定品种的动物接受了检查(我们对找出特定品种进行了多少次检查不感兴趣!)，我们可以运行此查询:

SELECT animals.animal_species, 
       animals.animal_breed, 
       Count(DISTINCT animals.animal_id) 
FROM   examinations, 
       animals 
WHERE  examinations.examination_animal_id = animals.animal_id 
       AND animals.animal_breed IS NOT NULL 
GROUP  BY animals.animal_species, 
          animals.animal_breed 
ORDER  BY animals.animal_species 

通过运行这个我们得到类似的东西:

dog | sheepdog    | 3
dog | collie      | 1
dog | terrier     | 5
cat | Persian cat | 3
cat | Birman cat  | 2

现在我想包括每个物种的总和。结果应如下所示:

dog | sheepdog    | 3 | 9
dog | collie      | 1 | 9
dog | terrier     | 5 | 9
cat | Persian cat | 3 | 5
cat | Birman cat  | 2 | 5

能否请您告诉我如何更改查询才能实现此目的？我尝试了几种解决方案，但都没有奏效......

非常感谢您!

Answer 1

我认为以下内容可以满足您的需求，但完全有可能提供更高效的解决方案。这会在您的代码中添加一个子查询，该子查询获取每个物种的总数，然后将该总数添加到选择中。我还用更现代和更受欢迎的等效项替换了您的“旧式”JOIN:

SELECT
    a.animal_species,
    a.animal_breed,
    COUNT(DISTINCT a.animal_id) as animals_examined,
    species_count.species_animals_examined
    FROM examinations e
JOIN animals a ON
    e.examination_animal_id = a.animal_id
JOIN 
    (SELECT
         a2.animal_species,
         count(distinct a2.animal_id) as species_animals_examined
    FROM examinations e2
    JOIN animals a2 ON
        e2.examination_animal_id = a2.animal_id
    WHERE
        a2.animal_breed IS NOT NULL
    GROUP BY a2.animal_species
    ) as species_count ON
    species_count.animal_species = a.animal_species
WHERE
    a.animal_breed IS NOT NULL
GROUP BY a.animal_species, a.animal_breed
ORDER BY a.animal_species