mysql - 我需要计算每个员工每月的平均产量

Question

我需要创建一份报告，显示平均每天至少生产 30 件的员工

我有 2 个表:

Employees: id, name, surname
Production: id, date_time, id_employee, [..], quantity_produced

员工表中的 id = 生产表中的 id_employee

<小时/>

SELECT name, surname, Avg(quantity_produced), Month(data_ora) Mnth
FROM production inner join employees on employees.id = production.id_employee
GROUP BY name, quantity_produced, Month(date_time);

或者

select name, surname, quantity_produced, avg(quantity_produced) as avgentrypermonth 
    from (
select month(date_time) as month ,count(1) as quantity
     group by month(date_time));

或者

select name, surname, quantity_produced
    from production 
     inner join employees on employees.id = production.id_employee
    where avg(quantity_produced) > 30;

Answer 1

我很可能会进行以下查询之一。

但是如果没有示例数据和预期结果，很难提出可靠的建议。

查询:

SELECT 
    Production.id_employee,
    AVG(Production.quantity_produced)
FROM 
    Production
WHERE
    Production.date_time BETWEEN
                         (LAST_DAY(NOW()) + INTERVAL 1 DAY) - INTERVAL 1 MONTH # first day of current month
                         AND
                         LAST_DAY(NOW()) # last day of current month
GROUP BY 
    Production.id_employee

查询何时需要员工记录:

SELECT 
    Employees.name,
    Employees.surname,
    AVG(Production.quantity_produced)
FROM 
    Production
INNER JOIN 
    Employees ON Production.id_employee = Employees.id
WHERE
    Production.date_time BETWEEN
                         (LAST_DAY(NOW()) + INTERVAL 1 DAY) - INTERVAL 1 MONTH  # first day of current month
                         AND
                         LAST_DAY(NOW()) # last day of current month
GROUP BY 
    Employees.id

请注意，上面的查询至少假定 MySQL 5.7.5+ 和 SQL 1999+ 标准可选功能，称为 functional dependency .

参见manual

或者作为相关子查询:正确索引时应该没问题

SELECT 
    (SELECT Employees.name FROM Employees.id = Production.id_employee) AS name,
    (SELECT Employees.surname FROM Employees.id = Production.id_employee) AS surname,
    AVG(Production.quantity_produced)
FROM 
    Production
WHERE
    Production.date_time BETWEEN
                         (LAST_DAY(NOW()) + INTERVAL 1 DAY) - INTERVAL 1 MONTH # first day of current month
                         AND LAST_DAY(NOW()) # last day of current month
GROUP BY 
    Production.id_employee