mysql - 从多个表中检索聚合值

Question

我试图从不同的表中获取一些聚合值，但我的问题是它们被错误地返回，即作为彼此的乘法。

我有这些表:

Institutes
----------
ID
name

Conversions
-----------
ID
institute_id
training_id

Trainings
---------
ID
institute_id

我想获取 Institute.name、转换次数和培训次数。我可以获得名称和其中一个聚合值，但是当我尝试获取这两个值时，它会返回错误的数字，因为它会为每次训练重复所有转换(或相反)。

我正在使用这个 SQL:

SELECT    Institute.id
          ,Institute.name 
          ,Institute.friendly_url
          ,count(Training.`id`) as totalTrainings
          ,count(Conversion.`id`) AS totalConversions
FROM      institutes Institute  
          LEFT JOIN    trainings Training     ON Institute.`id` = Training.`institute_id`       
          LEFT JOIN    conversions Conversion ON Training.`institute_id` =   Conversion.`institute_id`
GROUP BY  1,2,3

稍后我需要添加更多表，使用相同的结构并且唯一的目的是检索聚合。我可以通过使用多个查询来使其工作，但是当我想过滤数据时，我会遇到无法使用正确的排序、分页和问题的问题。

创建 View 也是我一直在考虑的解决方案，但我仍然需要一些 SQL 来创建该 View 。

我也试过修改 GROUP BY 但无济于事。

更新我现在在我的连接中使用子查询，这似乎有效。有没有更优的解决方案？

SELECT    Institute.id
          ,Institute.name 
          ,Institute.friendly_url
          ,Training.total
          ,Conversion.total
FROM      institutes Institute 
          LEFT JOIN (SELECT institute_id, count(id) AS total
                     FROM `trainings`
                     GROUP BY institute_id) AS Training ON Training.institute_id = Institute.id
          LEFT JOIN (SELECT institute_id, count(id) AS total
                     FROM `conversions`
                     GROUP BY institute_id) AS Conversion ON Conversion.institute_id = Institute.id

Answer 1

试试吧。做一个单独的子查询是更好的恕我直言。而且看起来也更好，更容易阅读查询。

SELECT    
Institute.id  
,Institute.name   
,Institute.friendly_url  
,(SELECT count(Training.`id`) FROM trainings Training WHERE Institute.`id` = Training.`institute_id` LIMIT 1) as totalTrainings   
,(SELECT count(Conversion.`id`) FROM conversions Conversion ON Institute.`id` =   Conversion.`institute_id`  LIMIT 1) AS totalConversions  
FROM institutes Institute