求一sql的算法
有以下員工表：
ID Name age department image inputDate
1Jim20133012006-01-01
2Tom22133022006-05-01
3Ben22133032006-03-01
4Sam20143042006-04-01
5Jion21143052006-05-01
6Carry20153062006-06-01
7Apple21153072006-01-11
8Cenny23153082007-01-01
9Lucy20153092006-02-01
10Sruory23154002007-01-01
我想在每個部門中找出一位員工，邏輯是：在本部門中age最大的，如age相同的選inputDate較小的，如inputDate也相同就選擇ID較小的。
我的方法如下：
declare @tbPerson table
(IDint ,
Namevarchar(20),
ageint,
departmentIDint,
ImageIDint,
InputDatedatetime,
UpdateDatedatetime
)
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 departmentID
select 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.MaxAge
select * 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
但我覺得這種方法比較煩，請教有無更好的方法呢？
[解决办法]
declare @tbPerson table
(IDint ,
Namevarchar(20),
ageint,
departmentIDint,
ImageIDint,
InputDatedatetime
)
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 @tbPerson
SELECT * 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)
