在mysql中通过分组使用平均值

2017-10-08 mysql sql select

我有三张桌子

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;

Answers

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

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

选择T1.NAME 来自T1公司的内部联接(选择COMPANY_ID,AVG(SALARY)AS的平均工资,来自SALARY GROUP BY COMPANY_ID的工资)作为T2 开启T1.ID = T2.COMPANY_ID 在哪里T2.avgSalary> = 40000;

Related