|
，人数，按部门号排序
select count(*) as 员工数,a.DEPARTMENT_ID as 部门号,avg(SALARY) as 平均工资 from EMPLOYEES as a
where (select count(c.EMPLOYEE_ID) from EMPLOYEES as c where c.DEPARTMENT_ID = a.DEPARTMENT_ID
and c.SALARY>(select avg(SALARY) from EMPLOYEES as b where c.DEPARTMENT_ID = b.DEPARTMENT_ID))>1
group by a.DEPARTMENT_ID order by a.DEPARTMENT_ID
select count(*) as 员工数,a.DEPARTMENT_ID as 部门号,avg(SALARY) as 平均工资 from EMPLOYEES as a
group by a.DEPARTMENT_ID order by a.DEPARTMENT_ID
select * from Employees
--12对于EMPLOYEES中低于自己工资至少5人的员工，列出其部门号，姓名，工资，以及工资少于自己的人数
select a.DEPARTMENT_ID,a.EMPLOYEE_NAME,a.SALARY,(select count(*) from EMPLOYEES as b
where b.SALARY < a.SALARY) as 人数 from EMPLOYEES as a
where (select count(*) from EMPLOYEES as b where b.SALARY5
?
|