mysql - 如何使用具有多个连接的聚合函数？

Question

我需要找到员工Milo的最大工作经验。这是我的表格:

CREATE TABLE company (
  id   INT          NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(250) NOT NULL
);

CREATE TABLE employee (
  id   INT          NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(250) NOT NULL
);

CREATE TABLE company_employee (
  company_id  INT NOT NULL,
  employee_id INT NOT NULL,
  hire_date   DATE DEFAULT NULL,
  resign_date DATE DEFAULT NULL,
  FOREIGN KEY (company_id) REFERENCES company (id),
  FOREIGN KEY (employee_id) REFERENCES employee (id)
);

我的查询获取了 Milo 的所有公司

SELECT
  employee.name, company.name, company_employee.hire_date, 
  company_employee.resign_date, 
  (company_employee.resign_date - company_employee.hire_date)   
FROM company
  JOIN company_employee
    ON (company.id = company_employee.company_id)
  JOIN employee
    ON (company_employee.employee_id = employee.id)
  WHERE employee.name = 'Milo'

返回

+------+--------------+-----------+-------------+-----------------+
| name | company_name | hire_date | resign_date | experience_days | 
+------+--------------+-----------+-------------+-----------------+
| Milo | IBM          |1997-04-17 | 1998-03-08  | 325             | 
| Milo | IBM          |2012-04-03 | 2014-02-15  | 683             |
| Milo | IBM          |2000-08-10 | 2003-01-01  | 874             |
+------+--------------+-----------+-------------+-----------------+

但我只需要一条具有最大经验的记录

+------+-----+------------+------------+-----+
| Milo | IBM | 2000-08-10 | 2003-01-01 | 874 |
+------+-----+------------+------------+-----+

更新:

此查询查找每个公司的所有员工数量

SELECT
  company.name,
  COUNT(company_employee.employee_id)
FROM company
  LEFT JOIN company_employee
    ON company.id = company_employee.company_id
GROUP BY company.name
HAVING COUNT(company_employee.employee_id) > 0;

我可以简化它吗？

Answer 1

您不需要聚合函数(MAX)，因为您没有在这里聚合任何内容。

您只需添加 ORDER BY 和 LIMIT 子句，例如:

ORDER BY experience_days DESC
LIMIT 1

整个查询可能是:

SELECT
  employee.name, company.name, company_employee.hire_date, 
  company_employee.resign_date, 
  (company_employee.resign_date - company_employee.hire_date)  as experience_days
FROM company
  JOIN company_employee
    ON (company.id = company_employee.company_id)
  JOIN employee
    ON (company_employee.employee_id = employee.id)
  WHERE employee.name = 'Milo'   
ORDER BY experience_days DESC
LIMIT 1

@更新

是的，您可以简化查询。

首先，当您寻找有员工的公司时，可以使用 JOIN 而不是 LEFT JOIN。

然后，因为您已经只获得有员工的公司(员工行必须存在才能与公司连接)，所以不需要使用 HAVING 条件。

SELECT
  company.name,
  COUNT(company_employee.employee_id)
FROM company
  JOIN company_employee
    ON company.id = company_employee.company_id
GROUP BY company.name;