SQL查询-前3个求和(Sal)

Question

嗨，我想从下面的查询中获得前3个没有重复的总和(工资)。我们不能使用rownum，因为它会给出重复项。有人能帮帮我吗？

SELECT d.dname, SUM(e.sal)
FROM emp e ,dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname;

Answer 1

   select dname,sum(distinct sal) from 
   (SELECT d.dname dname, sal,
    dense_rank() over (partition by e.deptno order by sal desc) rn 
    FROM emp e,dept d WHERE e.deptno = d.deptno) where rn<=3 group by dname;

在内部查询中，我查询了dname、以及通过使用dense_rank()函数，我已经将行号赋给了desc

中没有明智的薪水

例如：

dname  sal  rn
.....  ...  ...
acc    5000  1
acc    3000  2
acc    3000  2
acc    2000  3
bcc    4500  1
bcc    3000  2 .....etc

外部的distinct关键字我刚刚使用distinct关键字过滤了薪水

Answer 2

您可以尝试执行以下查询：

Select
  Agg_Sal.Dname, 
  Agg_Sal.Total_Sal,
  Rank() Over (Partition By Dname Order By Total_Sal Desc) Rank
From 
  (Select 
    D.Dname Dname, 
    Sum(Sal) As Total_Sal 
  From 
    Emp E, Dept D
  Where
    E.Deptno = D.Deptno
  Group By
    D.Dname) Agg_Sal
Where 
  Rank <= 3
Order By 
  Rank

它类似于Here上的上一个示例

Answer 3

如果你按薪水降序排序，那么rownum应该是有效的：

select *
  from (select d.dname, sum(e.sal)
          from emp e, dept d
         where e.deptno = d.deptno
         group by d.dname
         order by 2 desc)
 where rownum <= 3