日期:2014-05-19  浏览次数:20615 次

求一sql的算法
有以下員工表:

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