MySQL:多个内部连接产生倾斜计数

Question

我有三个表，tb_orders、tb_tasks&我正在尝试查询的tb_userconfig，但当使用两个内部联接语句运行查询时，COUNT()似乎完全倾斜了。然而，当尝试仅使用一个内部联接声明运行查询时一切似乎都正常(减去我缺少的一列(

示例表结构：

tb_orders

| OrderedBy | OrderDate    |
|-----------|--------------|
| persona   | 29/6/21 0:00 |
| persona   | 29/6/21 0:00 |
| personc   | 29/6/21 0:00 |
| personb   | 29/6/21 0:00 |

tb_userconfig

| EmployeeName | Username |
|--------------|----------|
| Person A     | persona  |
| Person B     | personb  |
| Person C     | personc  |

tb_tasks

| AssigneeUser |
|--------------|
| Persom A     |
| Person B     |
| Person C     |
| Person B     |

查询：

SELECT T1.EmployeeName, T1.Username, Count(T2.OrderedBy) AS OrderCount, Count(T3.AssigneeUser) AS TaskCount
FROM tb_userconfig AS T1
INNER JOIN (
SELECT OrderedBy, OrderDate FROM tb_orders
) AS T2 on T1.Username = T2.OrderedBy
INNER JOIN (
SELECT AssigneeUser FROM tb_tasks
) AS T3 on T1.EmployeeName = T3.AssigneeUser
GROUP BY T1.EmployeeName ORDER BY T1.EmployeeName

期望结果：

| EmployeeName | Username | OrderCount | TaskCount |
|--------------|----------|------------|-----------|
| Person A     | persona  | 2          | 1         |
| Person B     | personb  | 1          | 2         |
| Person C     | personc  | 1          | 1         |

有什么想法吗？为什么这个说法会导致计数失真，而不会产生预期的结果？

TIA-

Answer 1

您可以通过预聚合计数来简化。

在不了解您的数据的情况下，我建议使用外部联接和合并来处理无行，否则将排除有任务但没有订单的用户，反之亦然。

select uc.EmployeeName, uc.username,
Coalesce(o.OrderCount, 0) OrderCount, 
Coalesce(t.TaskCount, 0) TaskCount
from tb_userconfig uc
left join (
select orderedby, Count(*) OrderCount
from tb_orders
group by orderedby
) o on o.orderedby=uc.username
left join (
select AssigneeUser, Count(*) TaskCount
from tb_tasks
group by AssigneeUser
) t on t.AssigneeUser=uc.username

演示Fiddle