mysql - 在 mysql 中使用 group by 的平均值

Question

我有三个表

EMPLOYEE table with ID and NAME
COMPANY table with ID and NAME
and 
SALARY table with EMPLOYEE_ID, COMPANY_ID and SALARY

我想打印每个平均工资大于等于40000的公司的名字

我的查询是

Select distinct COMPANY.NAME from COMPANY,SALARY,EMPLOYEE WHERE SALARY.COMPANY_ID=COMPANY.ID and (select avg(SALARY) from SALARY,EMPLOYEE WHERE SALARY.EMPLOYEE_ID=EMPLOYEE.ID) >=40000;

Answer 1

您可以按公司名称分组并在 having 子句中设置条件:

SELECT   c.name
FROM     company c
JOIN     salary s ON c.id = s.company_id
GROUP BY c.name
HAVING   AVG(salary) >= 40000