具有多个计数的 MySQL 查询

Question

我对 MySQL 查询有点卡住了，需要一些帮助。

假设我们有一个包含这些列的“动物”表:

animal_id | animal_species
1         | dog
2         | cat
3         | dog
4         | pig

此外，我们还有一个“检查”表，其中包括对这些动物的各种检查:

examination_id | examination_animal_id
1              | 1
2              | 1
3              | 3
4              | 2
5              | 4

最后我们有另一个表“diagnoses_statistics”，其中包含一些检查的一个或多个诊断:

diagnoses_statistics_id | diagnoses_statistics_examination_id | diagnoses_statistics_diagnosis
1                       | 1                                   | luxation
2                       | 3                                   | thrombopathy
3                       | 3                                   | trauma
4                       | 3                                   | luxation
5                       | 5                                   | trauma

所有表格都包含许多其他列，我只是缩短了它们，因为上面的例子应该足以说明我的问题。

我们想知道每个物种有多少相同的诊断。为此，我们可以运行此查询:

SELECT animals.animal_species,
       diagnoses_statistics.diagnoses_statistics_diagnosis,
       COUNT(diagnoses_statistics.diagnoses_statistics_diagnosis) AS diagnoses_count
FROM examinations
JOIN animals ON animals.animal_id = examinations.examination_animal_id
JOIN diagnoses_statistics ON diagnoses_statistics.diagnoses_statistics_examination_id = examinations.examination_id
GROUP BY animals.animal_species,
         diagnoses_statistics.diagnoses_statistics_diagnosis
ORDER BY animals.animal_species,
         diagnoses_statistics.diagnoses_statistics_diagnosis

这给了我们这样的东西:

animal_species | diagnoses_statistics_diagnosis | diagnoses_count
dog            | luxation                       | 2
dog            | thrombopathy                   | 1
dog            | trauma                         | 1
pig            | trauma                         | 1

到目前为止，还不错。现在我们要添加一个列，其中包含包含任何诊断的每个物种的总和:

animal_species | diagnoses_statistics_diagnosis | diagnoses_count | species_count
dog            | luxation                       | 2               | 2
dog            | thrombopathy                   | 1               | 2
dog            | trauma                         | 1               | 2
pig            | trauma                         | 1               | 1

为了实现这一点，我尝试了这段代码，它似乎在“species_count”中产生了正确的值，但它打破了“diagnoses_count”列:

SELECT animals.animal_species,
       diagnoses_statistics.diagnoses_statistics_diagnosis,
       COUNT(diagnoses_statistics.diagnoses_statistics_diagnosis) AS diagnoses_count,
       species_count.species_count
FROM examinations
JOIN animals ON animals.animal_id = examinations.examination_animal_id
JOIN diagnoses_statistics ON diagnoses_statistics.diagnoses_statistics_examination_id = examinations.examination_id
JOIN
    (SELECT animals.animal_species,
            diagnoses_statistics.diagnoses_statistics_diagnosis,
            COUNT(animals.animal_id) AS species_count
     FROM examinations
     JOIN animals ON animals.animal_id = examinations.examination_animal_id
     JOIN diagnoses_statistics ON diagnoses_statistics.diagnoses_statistics_examination_id = examinations.examination_id
     GROUP BY animals.animal_species,
              diagnoses_statistics.diagnoses_statistics_diagnosis
    ) AS species_count ON species_count.animal_species = animals.animal_species
GROUP BY animals.animal_species,
         diagnoses_statistics.diagnoses_statistics_diagnosis
ORDER BY animals.animal_species,
         diagnoses_statistics.diagnoses_statistics_diagnosis

也许我做的完全错了。

你能帮我解决这个问题吗？

非常感谢您的提前帮助。

Answer 1

你很接近。我想这就是你什么。注意子查询中的 COUNT(DISTINCT ...):

SELECT
    a.animal_species,
    d.diagnoses_statistics_diagnosis,
    COUNT(1) as diagnoses_count,
    spc.species_count
FROM
    animals a
    JOIN examinations e ON
        e.examination_animal_id = a.animal_id
    JOIN diagnoses_statistics d ON
        d.diagnoses_statistics_examination_id = e.examination_id
    JOIN (SELECT 
              a2.animal_species,
              COUNT(DISTINCT a2.animal_id) as species_count
          FROM
              animals a2
              JOIN examinations e2 ON
                  e2.examination_animal_id = a2.animal_id
              JOIN diagnoses_statistics d2 ON
                  d2.diagnoses_statistics_examination_id = e2.examination_id
          GROUP BY
              a2.animal_species         
         ) spc ON
         spc.animal_species = a.animal_species
GROUP BY
    a.animal_species,
    d.diagnoses_statistics_diagnosis,  
    spc.species_count

您可以在 rextester.com/XXQ94494 尝试一下.向@P.Salmon 致敬以获取构建数据集的代码。