一道SQL的笔试题目，大家一起提供思路 ( 积分: 50 )

有两个表：
dept(deptno(PK), dname, location)
emp(empno(PK), ename, job, salary, deptno)
第一个表为部门表，里面包括部门编号（主键），部门名称和部门位置
第二个表为员工表，里面包括员工编号（主键），员工姓名、员工工作，工资和所在部门的外键
规定每个员工最多在一个部门。

1、列出所有部门的部门编号、部门名称，同时列出各个部门Job为Engineer的员工姓名和工作（提示：有的部门中并没有Eigneer工种的员工）
我是这么写的，但是这个结SQL有点问题，我不知道是MySQL的版本问题造成的还是我的SQL不正确：
select dept.deptno as 'Department No ', dname as 'Department Name ', ename as 'Employee Name ', job as 'Job '
from dept right outer join emp on dept.deptno=emp.deptno
where job= 'Engineer '

2、列出所有工资高于本部门平均工资的员工姓名、部门名称、平均工资、工资，工资升序排列
select emp_a.ename as 'Employee Name ', dname as 'Department Name ', avg(emp_a.salary) as 'AVG Salary ', emp_a.salary as 'Salary ' from emp as emp_a, dept
where emp_a.deptno=dept.deptno and emp_a.salary >(select avg(emp_b.salary) from emp as emp_b where emp_a.deptno=emp_b.deptno)
group by emp_a.deptno order by emp_a.salary
这条也有点问题，希望高手们给修正一下！

select a.deptno, a.dname, b.ename, b.job
from dept a left join emp b on a.deptno = b.deptno
where b.job is null or b.job = 'Engineer'


select a.ename, b.dname, c.ea, a.salary
from emp a left join dept b on a.deptno = b.deptno,(
select deptno, avg(salary) as ea
from emp
group by deptno
) c
where a.salary > c.ea
order by a.salary

1、题目要求列出所有的部门，并且提示有的部门可能没有Eigneer的工种，故此种情况只能用 Dept Left Join Emp, 而不是你讲的 Right Join, 因为用Right Join, 万一出现某个部门暂时无员工或无Engineer员工的的时候，部门就会少掉
第二点，联接条件一用 Dept.DeptNo = Emp.DeptNo肯定没错，但是在联接结果上用了 Where Job='Engineer'，我认为是不正确的，这样相当于是对联接的结果作了过滤，所以得到的结果肯定是有工程师员工的所有部门，故条件应采用 On Dept.DeptNo = Emp.DeptNo and Emp.Job ='Engineer'
最终结果
Select a.DeptNo, a.DName, b.EName, b.Job From Dept a left join Emp b on a.DeptNo= B.DeptNo and b.Job= 'Engineer'
2、这题我认为，可以把问题分解成两个部分，这样有利于理解
一、因为要求输出部门名称与员工名称，所以结果中肯定有 Dept 与 Emp联接的操作，所以第一部分，就是要得到员工部门与部门名称的数据，同时也要用到工资，所以一并带上，此部门语句如下
Select a.DeptNo, a.DName, b.EName, b.Salary From Dept a Inner Join Emp b On a.DeptNo = b.DeptNo
二、就是求得每个部分的平均工资，这个不用多说
select DeptNo, Avg(Salary) as AvgSalary From Emp Group by DeptNo
接下来，就是对求得的这两个结果，作联接，得到最终结果
select EName, x.DName, AvgSalary, Salary From
(Select a.DeptNo, DName, EName, Salary From Dept a Inner Join Emp b On a.DeptNo = b.DeptNo) x inner join
(select DeptNo, Avg(Salary) as AvgSalary From Emp Group by DeptNo) y on x.DeptNo = y.DeptNo and x.Salary > y.AvgSalary order by Salary

以上是个人的理解，仅供参考

select a.deptno, a.dname, b.ename, b.job
from dept a left join emp b on a.deptno = b.deptno
where b.job = 'Engineer'

select a.ename,c.dname,b.ea,a.salary
from emp a
left join
(select deptno, avg(salary) as ea
from emp
group by deptno
) b on a.deptno=b.deptno
left join dept c on c.deptno=a.deptno and b.deptno=c.deptno
where a.salary>b.ea
order by a.salary

在SQL SERVER 中测试没问题
不知MYSQL中行不行
提供个思路

谢谢了。但是我想知道，是不是levi提供的第二题的方法效率比较低呢？因为要做两次外连接呢。

第二个 ysai与 暗战用 外联肯定是错的，这个无需多说，楼主原本用内联是对的
之于效率问题，
因为条件emp_a.salary >(select avg(emp_b.salary) from emp as emp_b where emp_a.deptno=emp_b.deptno) 是在每求到一个人的工资后都要去计算这个人所在部门的平均资，所以要牺牲蛮多时间（如果员工数多的话）
况且做一次部门与员工的联接是肯定的，余下的就是那次与部门平均工资的联接，总体来说，应该效率不会低太多（毕竟正常情况下，肯定是员工数＞＞部门数）

补充一下，刚才看了一下楼主是另两位的回复
楼主本身的，能不能得到正确结果，尚在怀疑中，另外两位的，其实也都使用了两次以上的联接，不要以为只出现一次 Join就只有一次联接，其实 From 表a, 表b 是等同于 From 表a Inner Join 表b的
select EName, x.DName, AvgSalary, Salary From
(Select a.DeptNo, DName, EName, Salary From Dept a Inner Join Emp b On a.DeptNo = b.DeptNo) x,
(select DeptNo, Avg(Salary) as AvgSalary From Emp Group by DeptNo) y
Where x.DeptNo = y.DeptNo and x.Salary > y.AvgSalary order by Salary
与原先用Inner Join得到的是同样的效果

levi是个高手阿。呵呵！