mysql - MySql 中每个组的加入项目数

Question

我需要获得一组结果，显示每个“esta”组累积的项目数。

我按机构对结果进行分组。

设施内部连接到基地。

左连接的项目在底部连接。

所以在 Esta 组 2 中，假设有 3 个基本 ID。附加到基本 ID 的每条书面和口头记录都将计入结果集中的那个 esta。 每个基本记录可以附加多个“书面”或“口头”。

我在数据库中有 6 条口头记录和 4 条书面记录，它们分布在不同的“esta”记录中。在我的查询中，它们都计入我得到的结果的第一行。

我已经对更多的数据进行了相同的尝试，并且不管“esta”如何，第一行都包含每个左连接的元素一起计数。

SQL:

SELECT 
     esta.enf_esta_id
     ,SUM(IF(verbal.enf_verbal_id is not null,1,0)) as verbals
     ,SUM(IF(written.enf_written_id is not null,1,0)) as writtens
FROM 
     enf_base base
          INNER JOIN enf_esta esta ON esta.enf_esta_id = base.enf_esta_id
          LEFT JOIN enf_verbal verbal ON verbal.enf_base_id = base.enf_base_id
          LEFT JOIN enf_written written ON written.enf_base_id = base.enf_base_id
WHERE 
     1=1
GROUP BY 
     esta.enf_esta_id

结果:

enf_esta_id verbals writtens
2           10      10
3           1       0
4           1       1
6           0       0

为了证明第一行是不正确的，这里是从 enf_esta_id 2 中获取口头和书面结果。

SELECT 
     COUNT( * ) AS total
FROM 
     enf_written
         INNER JOIN enf_base ON enf_base.enf_base_id = enf_written.enf_base_id
         INNER JOIN enf_esta ON enf_base.enf_esta_id = enf_esta.enf_esta_id
WHERE 
     enf_esta.enf_esta_id =2

产量:

5

与 enf_verbal 相同，结果为 2。如果我们对问题查询结果的第一行进行折扣，则将每个的总数相加得到正确的 10。

谁能帮我得到我需要的结果？

Answer 1

你正在成倍增加。假设有 2 个口头的和 5 个书面的，那么你的连接会产生这 10 个记录(即所有组合)。而不是连接表然后聚合，你应该先聚合然后加入你的聚合。在您的情况下，这是每个基本 ID 的聚合，您最终将进一步聚合以获得 estas。

select 
  base.enf_esta_id,
  coalesce(sum(verbal.cnt), 0) as verbals,
  coalesce(sum(written.cnt), 0) as writtens
from enf_base base 
left join
(
  select enf_base_id, count(*) as cnt
  from enf_verbal
  group by enf_base_id
) verbal on verbal.enf_base_id = base.enf_base_id
left join
(
  select enf_base_id, count(*) as cnt
  from enf_written
  group by enf_base_id
) written on written.enf_base_id = base.enf_base_id
group by base.enf_esta_id;