求一sql的算法

Question

有以下員工表：ID       Name     age   department  image    inputDate 
1 Jim 20 13 301 2006-01-01 
2 Tom 22 13 302 2006-05-01 
3 Ben 22 13 303 2006-03-01 
4 Sam 20 14 304 2006-04-01 
5 Jion 21 14 305 2006-05-01 
6 Carry 20 15 306 2006-06-01 
7 Apple 21 15 307 2006-01-11 
8 Cenny 23 15 308 2007-01-01 
9 Lucy 20 15 309 2006-02-01 
10 Sruory 23 15 400 2007-01-01 我想在每個部門中找出一位員工，邏輯是：在本部門中age最大的，如age相同的選inputDate較小的，如inputDate也相同就選擇ID較小的。我的方法如下：
declare  @tbPerson table
(ID int ,
Name varchar(20),
age int,
departmentID int,
ImageID int,
InputDate datetime,
UpdateDate datetime
)insert into @tbPerson
select 1,'Jim',20,13,301,'2006-01-01'
union all
select 2,'Tom',22,13,302,'2006-05-01'
union all
select 3,'Ben',22,13,303,'2006-03-01'
union all
select 4,'Sam',20,14,304,'2006-04-01'
union all
select 5,'Jion',21,14,305,'2006-05-01'
union all
select 6,'Carry',20,15,306,'2006-06-01'
union all
select 7,'Apple',21,15,307,'2006-01-11'
union all
select 8,'Cenny',23,15,308,'2007-01-01'
union all
select 9,'Lucy',20,15,309,'2006-02-01'
union all
select 10,'Sruory',23,15,400,'2007-01-01'select departmentID,max(age) as MaxAge
into #tbMaxAge
from @tbPerson
group by departmentIDselect a.departmentID,
                a.MaxAge,
          min(b.InputDate) as MinDate
into #tbMaxAge_InputDate
from #tbMaxAge as a
inner join @tbPerson as b
on a.departmentID=b.departmentID
  and a.MaxAge=b.age
group by a.departmentID,
                a.MaxAgeselect * from @tbPerson where ID in(
select min(ID)
from #tbMaxAge_InputDate as a
inner join @tbPerson as b
on a.departmentID=b.departmentID
  and a.MaxAge=b.age
  and a.MinDate=b.InputDate
group by  a.departmentID,
a.MaxAge,
a.MinDate
)drop table #tbMaxAge
drop table #tbMaxAge_InputDate但我覺得這種方法比較煩，請教有無更好的方法呢？

Answer 1

declare  @tbPerson table
(ID int ,
Name varchar(20),
age int,
departmentID int,
ImageID int,
InputDate datetime
)insert into @tbPerson
select 1,'Jim',20,13,301,'2006-01-01'
union all
select 2,'Tom',22,13,302,'2006-05-01'
union all
select 3,'Ben',22,13,303,'2006-03-01'
union all
select 4,'Sam',20,14,304,'2006-04-01'
union all
select 5,'Jion',21,14,305,'2006-05-01'
union all
select 6,'Carry',20,15,306,'2006-06-01'
union all
select 7,'Apple',21,15,307,'2006-01-11'
union all
select 8,'Cenny',23,15,308,'2007-01-01'
union all
select 9,'Lucy',20,15,309,'2006-02-01'
union all
select 10,'Sruory',23,15,400,'2007-01-01'SELECT * FROM @tbPersonSELECT * FROM @tbPerson t1
WHERE NOT EXISTS 
(SELECT 1
FROM @tbPersON t2
WHERE t2.departmentID=t1.departmentID
AND 
((t2.age>t1.age) OR (t2.age=t1.age AND t2.InputDate<t1.InputDate)
OR (t2.age=t1.age AND t2.InputDate=t1.InputDate AND t2.id<t1.id)) )
(10 row(s) affected)ID          Name                 age         departmentID ImageID     InputDate                                              
----------- -------------------- ----------- ------------ ----------- ------------------------------------------------------ 
1           Jim                  20          13           301         2006-01-01 00:00:00.000
2           Tom                  22          13           302         2006-05-01 00:00:00.000
3           Ben                  22          13           303         2006-03-01 00:00:00.000
4           Sam                  20          14           304         2006-04-01 00:00:00.000
5           Jion                 21          14           305         2006-05-01 00:00:00.000
6           Carry                20          15           306         2006-06-01 00:00:00.000
7           Apple                21          15           307         2006-01-11 00:00:00.000
8           Cenny                23          15           308         2007-01-01 00:00:00.000
9           Lucy                 20          15           309         2006-02-01 00:00:00.000
10          Sruory               23          15           400         2007-01-01 00:00:00.000(10 row(s) affected)ID          Name                 age         departmentID ImageID     InputDate                                              
----------- -------------------- ----------- ------------ ----------- ------------------------------------------------------ 
3           Ben                  22          13           303         2006-03-01 00:00:00.000
5           Jion                 21          14           305         2006-05-01 00:00:00.000
8           Cenny                23          15           308         2007-01-01 00:00:00.000(3 row(s) affected)