)avgSalary从 部门
返回:
会计2916.666667
研究 </跨度> 2175.000000销售1566.666667
营运 </跨度>
但我不知道如何将它们结合起来,或者它是否可能:
‘JONES’,‘2975.00’,‘ 研究 </跨度> ’,‘‘2073.214286’‘BLAKE’,‘2850.00’,‘SALES’,‘2073.214286’‘CLARK’,‘2450.00’,’会计’,‘2073.214286’‘SCOTT’,‘3000.00’,‘ 研究 </跨度> ’,’‘2073.214286’’KING锟
有点像这样......?
mysql> select * -> from employee e -> where e.employee_salary > ( -> select avg(employee_salary) avg -> from employee ee -> -- this is how you join them without a join... -> where ee.department_id=e.department_id -> ); +-------------+---------------+---------------+-----------------+ | employee_id | department_id | employee_name | employee_salary | +-------------+---------------+---------------+-----------------+ | 2 | 1 | employee 2 | 40 | | 6 | 1 | employee 6 | 50 | +-------------+---------------+---------------+-----------------+ 2 rows in set (0.04 sec)
哪里....
mysql> select * from employee; +-------------+---------------+---------------+-----------------+ | employee_id | department_id | employee_name | employee_salary | +-------------+---------------+---------------+-----------------+ | 1 | 1 | employee 1 | 20 | | 2 | 1 | employee 2 | 40 | | 3 | 1 | employee 3 | 30 | | 4 | 1 | employee 4 | 30 | | 5 | 1 | employee 5 | 30 | | 6 | 1 | employee 6 | 50 | +-------------+---------------+---------------+-----------------+ 6 rows in set (0.00 sec)
没有加入......
mysql> select e.*, -> -> (select department_name -> from department d -> where d.department_id=e.department_id) as department_name, -> -> (select avg(employee_salary) avg -> from employee ee -> where ee.department_id=e.department_id) as department_average -> -> from employee e -> -> where e.employee_salary > ( -> select avg(employee_salary) avg -> from employee ee -> where ee.department_id=e.department_id -> ); +-------------+---------------+---------------+-----------------+-----------------+--------------------+ | employee_id | department_id | employee_name | employee_salary | department_name | department_average | +-------------+---------------+---------------+-----------------+-----------------+--------------------+ | 2 | 1 | employee 2 | 40 | department 1 | 33.333333333333336 | | 6 | 1 | employee 6 | 50 | department 1 | 33.333333333333336 | +-------------+---------------+---------------+-----------------+-----------------+--------------------+ 2 rows in set (0.00 sec)
除非真的有必要,否则你应该真的避免在select中加入,因为在现实生活中使用连接更好(为了提高数据集的效率/性能)。中的子查询 where 但是,这是完全合适的。
where