# 求一sql的算法解决思路

www.MyException.Cn  网友分享于：2013-03-24  浏览：10次

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

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   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
(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 '