分组查询取得前几名的sql语句编写－－我给自己的命题

        这个问题是我给自己提出的的一个问题，以验证sql语言是否“无所不能”，经过很久的思考和酝酿，现在终于有个几个答案，现在记录在blog中。

        命题：选取每个部门中年龄前两名的人员姓名和年龄。

 

答案一：

select * from (
select * from
(select * from scott.empb minus select * from scott.empb a where num=(select max(num) from scott.empb b where a.dept=b.dept)) a
where num=(select max(num) from (select * from scott.empb minus select * from scott.empb a where num=(select max(num) from scott.empb b where a.dept=b.dept))
          b where a.dept=b.dept)
union
 select * from scott.empb a where num=(select max(num) from scott.empb b where a.dept=b.dept)
) order by dept,num desc

－－－－－－这个是技巧的迭代，思路是“找出部门年龄最大的人做表A，在人员empb表中剔除这些人，剩下的人找出年龄最大的人，然后union上表A.问题解决”

 

答案二：

begin
for cc in (select distinct dept from scott.empb) loop
for aa in (select *  from (select * from scott.empb where dept=cc.dept order by num desc) a where rownum <3) loop
dbms_output.put_line(aa.id||','||aa.name||','||aa.dept||','||aa.num);
end loop;
end loop;
end;
/

 

－－－－－主要技巧是，先取一个部门，找出年纪最老的前两人，在取一个部门，找出前两人，知道无部门可取，循环结束。

 

答案三：  此方法是老杨提供的

       利用oracle8.1.6开始支持的“分析函数”，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。

select * from (select id,name,dept,num, count(*) OVER(PARTITION BY a.dept ORDER BY a.num desc) aa   from   scott.empb  a)
where aa<3

 

是否还有别的方法我不确定，不过我会继续思考，一有所得会在此补充