员工表emp(id,name,dept_id,salary,) 部门表dept(id,name)
查询工资低于部门平均工资的员工的姓名和工资
------解决方案--------------------------------------------------------
- SQL code
WITH B AS (SELECT avg(salary) as salary,dept_id FROM emp GROUP BY dept_id)SELECT A.id,A.nameFROM emp A,BWHERE A.dept_id=B.dept_idAND A.salary<B.salary;