一条SQL语句：：查询工资低于部门平均工资的员工的姓名和工资！帮帮忙！在线等

[gongzhengwei]

员工表emp(id,name,dept_id,salary,) 部门表dept(id,name)

查询工资低于部门平均工资的员工的姓名和工资

[jinjazz]

WITH B AS (SELECT avg(salary) as salary,dept_id FROM emp GROUP BY dept_id)
SELECT A.id,A.name
FROM emp A,B
WHERE A.dept_id=B.dept_id
AND A.salary<B.salary;

[fraul]

select A.name, A.salary from emp as A, (select avg(salary) as avg_salary, dept_id from emp group by dept_id)) as B
where A.dept_id = B.dept_id and A.salary > B.avg_salary

[HelloWorld_001]

2人思路是一样的。
但1楼是对的，2楼主要问题是最后的and A.salary > B.avg_salary
还有就是from table a,不能是 as a

[knowledge_Is_Life]

这个简单啊,网上搜一下就得到答案了.

[jxwuyuanyong]

select a.name, a.salary from emp a where salary<(select avg(salary) from emp where a.dept_id=dept_id group by dept_id);

[又是违规昵称]

效率的高低,没有分析过数据库的情况，单凭sql能下结论吗

[raoxy1982]

select a.*,b.salary as avg_salary from emp a,(
select dept_id,avg(salary) salary
from emp
group by dept_id) as b
where a.dept_id=b.dept_id and a.salary<b.salary

[yxiaowang]

select * from
(select a.name,a.salary,avg(a.salary) over (PARTITION BY dept_id) as Average from emp a)
where salary < Average

教科书写法应该是这样！！！

[xudaqing2008]

SELECT A.*
FROM EMP A,
(
select DEPT_ID,avg(SALARY) SALARY
from EMP
group by DEPT_ID
) B
WHERE A.DEPT_ID = B.DEPT_ID AND A.SALARY < B.SALARY

[xudaqing2008]

不过论效率，还是7楼的高啊！

[xdyy_2006]

jf

[wj6625]

学习到了~

[8193102]

各有各的招，招招见效就行

[thunderclap]

员工表emp(id,name,dept_id,salary,) 部门表dept(id,name)
查询工资低于部门平均工资的员工的姓名和工资

select e1.name, e1.salary
from emp e1
where e1.salary < (select avg(e2.salary)
                   from emp e2
                   group by e2.dept
                   having e2.dept = e1.dept)

[thunderclap]

select e1.name, e1.salary
from emp e1
where e1.salary < (select avg(e2.salary)
                   from emp e2
                   where e2.dept = e1.dept)

这样效率高一点

[网站推广优化yetaoaiueo]

WITH B AS (SELECT avg(salary) as salary,dept_id FROM emp GROUP BY dept_id)
SELECT A.id,A.name
FROM emp A,B
WHERE A.dept_id=B.dept_id
AND A.salary<B.salary;

[peacexiazhu]

让我看看

[lxdhdgss]

14楼的非常正确

[zhrwty122886]

select e.name,salary,avg(salary)
from emp e,dept d
where e.dept_id=d.id
group by e.dept_id
having salary<avg(salary)