想得头疼,求个sql语句

Question

有两张表: 雇员表 EMPLOYEES，部门表 DEPARTMENTS.EMPLOYEES有name列，有salary（工资）列，有department_id列。DEPARTMENTS有department_id列和department_name列。想用个sql语句，查出department_name为“abc”，salary大于本部门平均工资的员工的名字。我自己写了好几次，总有问题，请教高手！

Answer 1

WITH B AS (SELECT avg(salary) as salary,department_id FROM EMPLOYEES GROUP BY department_id )SELECT A.id,A.name
FROM EMPLOYEES  A, EMPLOYEES   B
WHERE A.department_i=B.department_i
AND A.salary>B.salary;

Answer 2

department_id 相等吧.
select 
 e.[name]
from 
 [EMPLOYEES] e, [DEPARTMENTS] d 
where
 e.[department_id] = d.[department_id]
 and e.[salary] > (select sum(salary) from [EMPLOYEES]) / (select count(salary) from [EMPLOYEES]) 
 and d.[department_name] = 'abc'

Answer 3

select EMPLOYEES.name,(select avg(salary) from EMPLOYEES) as avgSalary from EMPLOYEES,DEPARTMENTS where DEPARTMENTS.department_name='abc' and EMPLOYEES.salary>avgSalary ;

Answer 4

见到高人了~可是为什么用不上departments表呢，不用departments表怎么比较department_name是否为“abc”？另外，请问有没办法把两句话合一下？谢谢~

Answer 5

SELECT emp.NAME
  FROM EMPLOYEES emp,
       (
         SELECT DEPARTMENT_ID, 
                AVG(SALARY) AS AVG_SAL
           FROM EMPLOYEES
       GROUP BY DEPARTMENT_ID
       ) sal,
       DEPARTMENTS dept
 WHERE emp.DEPARTMENT_ID = sal.DEPARTMENT_ID
   AND emp.DEPARTMENT_ID = dept.DEPARTMENT_ID
   AND emp.SALARY > sal.AVG_SAL
   AND dept.DEPARTMENT_NAME = 'abc'
   

Answer 6

with 是Oracle里的关键字，如梦写的整句SQL是正确的，只是少关联了departments而已。

Answer 7

select 
 e.[name]
from 
 [EMPLOYEES] e, [DEPARTMENTS] d 
where
 e.[department_id] = d.[department_id]
 and e.[salary] > (select avg(salary) from [EMPLOYEES])  
 and d.[department_name] = 'abc'