if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (人员 nvarchar(2),批次 int,部门 int,数量 int,状态 nvarchar(2))
insert into [TB]
select 'A',1,90,888,'N' union all
select 'B',1,80,888,'N' union all
select 'C',1,70,888,'Y' union all
select 'A',2,90,888,'Y' union all
select 'B',2,80,888,'Y' union all
select 'C',2,70,888,'Y' union all
select 'A',3,90,888,'N' union all
select 'B',3,80,888,'N' union all
select 'C',3,70,888,'Y' union all
select 'A',4,90,888,'Y' union all
select 'B',4,80,888,'Y' union all
select 'C',4,70,888,'Y'

select * from [TB]



SELECT  A.人员,A.部门,A.数量
FROM    TB A
WHERE   状态 = 'Y'
        AND NOT EXISTS ( SELECT 1
                         FROM   TB B
                         WHERE  A.人员 = B.人员
                                AND A.批次 < B.批次
                                AND A.部门 = B.部门 )
                                
                                
/*
人员    部门    数量
A    90    888
B    80    888
C    70    888
  */