select ename, salary, deptno from emp_xxx a where salary < (
select avg(salary) from emp_xxx b where b.deptno = a.deptno
);

select a.ename, a.salary, a.deptno 
from emp_xxx a
join (select deptno,avg(salary) as avs from emp_xxx group by deptno) b
on a.deptno = b.deptno
where a.salary < b.avs

------解决方案--------------------
SQL code
select a.ename,a.salary,a.deptno from emp_xxx a inner join (select avg(salary) salary,deptno from emp_xxx ) b on b.deptno = a.deptno and a.salary<b.salary

------解决方案--------------------
探讨

好不容易转换为连接，怎么还是有子查询


引用:
SQL code
select a.ename, a.salary, a.deptno
from emp_xxx a
join (select deptno,avg(salary) as avs from emp_xxx group by deptno) b
on a.deptno = b.deptno
where……


------解决方案--------------------
SQL code

;with cte as
(SELECT   deptno,
          Avg(salary) AS avs
             FROM     emp_xxx
             GROUP BY deptno)

SELECT a.ename,
       a.salary,
       a.deptno
FROM   emp_xxx a
       JOIN  cte b
         ON a.deptno = b.deptno
WHERE  a.salary < b.avs

------解决方案--------------------
05以后的写法。sql server

------解决方案--------------------
没说过子查询一定会慢

------解决方案--------------------
这个叫CTE，共同表表达式，是sql好像99以后的标准，就算Oracle、db2也支持的。

------解决方案--------------------
我个人的理解是同时执行，因为是关联的。