高人，指点下 SQL分组、排名查询

[解决办法]
先建个表：

--drop table tb


create table tb(
id int,姓名 varchar(10),
专业 varchar(10) ,
部门  varchar(10),成绩 int
)

insert into tb
select 1,'a1','通信','生产第一部',98 union all
select 2,'a2','通信','生产第一部',98 union all
select 3,'a3','通信','生产第一部',99 union all
select 4,'a4','通信','生产第一部',67 union all
select 5,'a5','信号','生产第一部',56 union all
select 6,'a6','信号','生产第一部',55 union all
select 7,'a7','信号','生产第二部',66 union all
select 8,'a8','信号','生产第二部',78 union all
select 9,'a9','信号','生产第二部',98 union all
select 10,'a10','信号','生产第三部',76 union all
select 11,'a11','供电','生产第三部',55 union all
select 12,'a12','供电','生产第三部',45 union all
select 13,'a13','供电','生产第三部',67 
go

--1.
select id ,姓名,专业,部门,成绩
from 
(
select *,
       dense_rank() over(partition by 部门 order by 成绩 desc) rownum
from tb 
)a
where rownum <= 2
order by id
/*
id姓名专业部门成绩
1a1通信生产第一部98
2a2通信生产第一部98
3a3通信生产第一部99
8a8信号生产第二部78
9a9信号生产第二部98
10a10信号生产第三部76
13a13供电生产第三部67
*/


--2.
select id ,姓名,专业,部门,成绩
from 
(
select *,
       dense_rank() over(partition by 专业 order by 成绩 desc) rownum
from tb 
)a
where rownum <= 2
order by id
/*
id姓名专业部门成绩
1a1通信生产第一部98
2a2通信生产第一部98
3a3通信生产第一部99
8a8信号生产第二部78
9a9信号生产第二部98
11a11供电生产第三部55
13a13供电生产第三部67
*/