oracle emp表 对于工资高于本部门平均水平，人数多于一人，列出部门号 工资 按部门号进行排序

Question

对于工资高于本部门平均水平，人数多于一人，列出部门号 工资 按部门号进行排序
select e.ename,dname,e.sal,e.deptno from emp1 e,dept1,
(select deptno,round(avg(sal))avgsal from emp1 group by deptno)a 
where e.deptno = a.deptno and e.sal >a.avgsal and e.deptno = dept1.deptno
order by e.deptno
如何判断人数多于一人

Answer 1

select *
from(
select e.*,row_number()over(partition by t.deptno order by t.avg_sal)row_num
from
(
select e.deptno,avg(e.sal) avg_sal
from emp e,dept d
where e.deptno=d.deptno
group by e.deptno
)t,emp e
where t.deptno=e.deptno and
e.sal > t.avg_sal) tt
where row_num >1;

Answer 2

SELECT ename,
  dname,
  sal,
  deptno
FROM
  (SELECT A.ename,
    b.dname,
    A.sal,
    A.empno,
    b.deptno,
    COUNT(1)OVER(PARTITION BY A.deptno)counts,
    AVG(a.sal)over(partition BY a.deptno)avg_sal
  FROM emp A,
    dept b
  WHERE A.deptno=b.deptno
  )
WHERE sal >avg_sal
AND counts>1

Answer 3

对！是要少一个人。因为我用了row_num >1;
这个sql有点问题，不能够满足你的需要。

Answer 4

--高于本部门平均薪水的人数多于一人
WITH tt AS
 (SELECT a.ename, a.sal, a.deptno
    FROM emp a,
         (SELECT AVG(t.sal) avgsal, t.deptno FROM emp t GROUP BY t.deptno) b
   WHERE a.deptno = b.deptno
     AND a.sal > b.avgsal)
SELECT tt.ename, tt.sal, tt.deptno, d.dname
  FROM tt, dept d
 WHERE tt.deptno = d.deptno
   AND tt.deptno IN
       (SELECT tt.deptno FROM tt GROUP BY tt.deptno HAVING COUNT(1) > 1)
 ORDER BY tt.deptno, tt.sal;

Answer 5

select a.* 
     ,sum(case when sal>avg_sal then 1 else 0 end) rich_count
from
(
select e.ename, dname, e.sal, e.deptno
       ,avg(sal) over(PARTITION BY e.deptno) avg_sal
  from emp1 e, dept1, where e.deptno = dept1.deptno
 order by e.deptno
) a

Answer 6

select deptno, avg_sal, count(1)
  from (select deptno, sal, avg(sal) over(partition by deptno) avg_sal
          from emp) t1
 where t1.sal > t1.avg_sal
 group by deptno, avg_sal
having count(1) > 1

Answer 7

select e.ename,f.dname,e.sale,e.deptno from emp1 e inner join  dept1 f on f.deptno =e.deptno inner join 
(select a.deptno from emp1  a  inner join  (select avg(sale) avgsal,deptno from emp1 group by deptno) 
b on b.deptno=a.deptno and a.sale>b.avgsal group by a.deptno having count(a.ename)>=2) c on c.deptno =e.deptno order by e.deptno