MYSQL sum/Count 在内部连接组内失败

分享于2022年07月17日 database group-by left-join mysql sql 问答
【问题标题】:MYSQL sum/Count 在内部连接组内失败(MYSQL sum/Count fails inside inner join group by)
【发布时间】:2022-01-24 08:32:37
【问题描述】:

我有一个包含销售记录的表:

Sale ID EmployeeId(FK) Employee 2 ...
1 101 Null ...
2 102 Null ...
3 300 Bob ...
... ... ... ...

我有另一个包含员工记录的表:

EmployeeId EmployeeName ...
101 Amanda ...
102 Bob ...
... ... ...
300 cicilia ...
... ... ...

我正在尝试选择获取所有销售额并按员工分组以进行绩效分析。到目前为止,我设法弄清了员工及其销售数量和总数。问题是我的销售记录中的第三列称为employee2,它可以为空,因为并非每笔销售都有另一名员工协助。与第二列不同,它没有索引到员工表。

例如在我下面的查询中,预期结果应该是 Amanda has 1 salecount, 0 helpCount,同时 Boss has 1 salecount, 1 helpCount,而 cicilia has 1 salecount, 0 helpcount。但是我得到了 1 个正确的销售计数,但鲍勃得到了 0 个帮助计数。到目前为止,这是我的查询:

select employee.employee_id, 
       employee.employee_Name, 
       count(sale.sale_id) as saleCount, 
       sum(sale.grand_total) as totalSalesRevenue, 
       sum(CASE WHEN sale.employee2 = employee.employee_Name THEN 1 
                ELSE 0 END) as helperEmpCount
from employee 
inner join sale on employee.employee_id = sale.employee_id 
group by employee.employee_id;

结果集,其中 helpCounts 不应为 0。 MYSQL sum/Count fails inside inner join group by 我正在运行一个 mysql 8.0 数据库。

编辑:我找到了一种解决方法,尽管效率很低。如果我将计数更改为嵌套选择,它会起作用,但考虑到我有很多员工,这会大大降低性能。

新查询:

select employee.employee_id, 
       employee.employee_Name, 
       count(sale.sale_id) as saleCount, 
       sum(sale.grand_total) as totalSalesRevenue, 
       (select count(sale.employee2) from sale where sale.employee2= employee_Name) as helperEmpCount
from employee 
inner join sale on employee.employee_id = sale.employee_id 
group by employee.employee_id;

知道如何提高效率吗?

  • 员工姓名是否唯一?
  • 是的,在雇员表中它是唯一的,但在销售表中不是。
  • @Oirampok 你能添加一些记录吗?在当前示例中,您将没有 helpCount 。您正在尝试比较 employee2 employee_Name 但没有具有相同 employee_id 的记录
  • 取消透视您的数据。使用 UNION 子查询作为基础而不是单个 sale 表。
  • @DanilaGanchar bob 应该有 1 个 helpCount,因为 ID 为 3 的销售记录有一名员工,即 cicilia,bob 作为助手。因此,该行将计为 cicilia 的一项销售计数和 bob 的一项帮助计数。

【解决方案1】:

您可以根据 2 个条件中的任何一个连接表并使用条件聚合:

SELECT e.employee_id, 
       e.employee_Name, 
       SUM(s.employee_id = e.employee_id) AS saleCount, 
       SUM(CASE WHEN s.employee_id = e.employee_id THEN s.grand_total ELSE 0 END) AS totalSalesRevenue, 
       SUM(s.employee2 = e.employee_Name) AS helperEmpCount
FROM employee e LEFT JOIN sale s
ON s.employee_id = e.employee_id OR s.employee2 = e.employee_Name
GROUP BY e.employee_id;