请高手看看，求一个SQL语句，稍微有些难吧！
有表名称和结构如下：
TestTable
KeyID varchar(4) Primary Key
StdID varchar(2)
SDate datetime
EDate datetime
-------------------------------------
有如下记录
KeyID StdID SDate EDate
A001 T1 2006/10/13 2006/10/15
A002 T1 2006/10/13 2006/11/10
A003 T2 2006/12/01 2006/11/05
A004 T2 2006/12/02 2006/12/08
A005 T2 2006/12/02 2006/12/09
--------------------------------------
想要结果是：不同的StdID,且SDate是最大的记录，如果SDate相同取EDate最大，
既想要的结果为：
KeyID StdID SDate EDate
A002 T1 2006/10/13 2006/11/10
A005 T2 2006/12/02 2006/12/09
请各位赐教，谢谢！
[解决办法]
declare @a table(KeyID varchar(4) Primary Key,StdID varchar(2),SDate datetime,EDate datetime)
insert @a select 'A001 ', 'T1 ', '2006/10/13 ', '2006/10/15 '
union all select 'A002 ', 'T1 ', '2006/10/13 ', '2006/11/10 '
union all select 'A003 ', 'T2 ', '2006/12/01 ', '2006/11/05 '
union all select 'A004 ', 'T2 ', '2006/12/02 ', '2006/12/08 '
union all select 'A005 ', 'T2 ', '2006/12/02 ', '2006/12/09 '
select stdid,sdate,max(edate) edate from @a group by stdid,sdate
select * from @a a where not exists(select 1 from
(select stdid,sdate,max(edate) edate from @a group by stdid,sdate) b where stdid=a.stdid and edate> a.edate)
[解决办法]
select * from TestTable a
where not exists (select 1 from TestTable where StdID = a.StdID and
(SDate > a.SDate or SDate = a.SDate and EDate > a.EDate))
[解决办法]
create table TestTable(
KeyID varchar(4) Primary Key,
StdID varchar(2),
SDate datetime,
EDate datetime
)
insert TestTable select 'A001 ', 'T1 ', '2006-10-13 ', '2006-10-15 '
union all select 'A002 ', 'T1 ', '2006-10-13 ', '2006-11-10 '
union all select 'A003 ', 'T2 ', '2006-12-01 ', '2006-11-05 '
union all select 'A004 ', 'T2 ', '2006-12-02 ', '2006-12-08 '
union all select 'A005 ', 'T2 ', '2006-12-02 ', '2006-12-09 '
select * from TestTable as tmp
where not exists(select 1 from TestTable where StdID=tmp.StdID and SDate> =tmp.SDate and EDate> tmp.EDate)
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb
(
KeyID varchar(4) Primary Key,
StdID varchar(2),
SDate datetime,
EDate datetime,
)
insert into tb(KeyID,StdID,SDate,EDate) values( 'A001 ', 'T1 ', '2006/10/13 ', '2006/10/15 ')
insert into tb(KeyID,StdID,SDate,EDate) values( 'A002 ', 'T1 ', '2006/10/13 ', '2006/11/10 ')
insert into tb(KeyID,StdID,SDate,EDate) values( 'A003 ', 'T2 ', '2006/12/01 ', '2006/11/05 ')
insert into tb(KeyID,StdID,SDate,EDate) values( 'A004 ', 'T2 ', '2006/12/02 ', '2006/12/08 ')
insert into tb(KeyID,StdID,SDate,EDate) values( 'A005 ', 'T2 ', '2006/12/02 ', '2006/12/09 ')
select m.* from
(
select a.* from tb a,
(select stdid , max(sdate) as sdate from tb group by stdid) b
where a.stdid = b.stdid and a.sdate = b.sdate
) m,
(
select p.* from
(
select a.* from tb a,
(select stdid , max(sdate) as sdate from tb group by stdid) b
where a.stdid = b.stdid and a.sdate = b.sdate
) p,
(
select stdid , max(edate) as edate from
(
select a.* from tb a,
(select stdid , max(sdate) as sdate from tb group by stdid) b
where a.stdid = b.stdid and a.sdate = b.sdate
) q
group by stdid
) o
where p.stdid = o.stdid and p.edate = o.edate
) n
where m.stdid = n.stdid and m.edate = n.edate
drop table tb
KeyID StdID SDate EDate
----- ----- ----------------------- -----------------------
A002 T1 2006-10-13 00:00:00.000 2006-11-10 00:00:00.000
A005 T2 2006-12-02 00:00:00.000 2006-12-09 00:00:00.000
（所影响的行数为 2 行）
[解决办法]
--取A006,
select * from TestTable
inner join
(select max(KeyID) as KeyID from (上面人的句) A group by StdID) T
on TestTable.KeyID=T.KeyID
[解决办法]
改改:
declare @a table(KeyID varchar(4) Primary Key,StdID varchar(2),SDate datetime,EDate datetime)
insert @a select 'A001 ', 'T1 ', '2006/10/13 ', '2006/10/15 '
union all select 'A002 ', 'T1 ', '2006/10/13 ', '2006/11/10 '
union all select 'A003 ', 'T2 ', '2006/12/01 ', '2006/11/05 '
union all select 'A004 ', 'T2 ', '2006/12/02 ', '2006/12/08 '
union all select 'A005 ', 'T2 ', '2006/12/02 ', '2006/12/09 '
select * from @a where KeyID in
(select
(select top 1 KeyID from @a b where b.StdID=a.StdID and SDate in(select max(SDate)
from @a group by StdID)order by KeyID desc)KeyID
from @a a group by StdID)
[解决办法]
create table tb(
KeyID varchar(4) Primary Key,
StdID varchar(2),
SDate datetime,
EDate datetime
)
insert tb select 'A001 ', 'T1 ', '2006-10-13 ', '2006-10-15 '
union all select 'A002 ', 'T1 ', '2006-10-13 ', '2006-11-10 '
union all select 'A003 ', 'T2 ', '2006-12-01 ', '2006-11-05 '
union all select 'A004 ', 'T2 ', '2006-12-02 ', '2006-12-08 '
union all select 'A005 ', 'T2 ', '2006-12-02 ', '2006-12-09 '
union all select 'A006 ', 'T2 ', '2006-12-02 ', '2006-12-09 '
SELECT IDENTITY(INT,1,1) AS id,* INTO #t FROM tb ORDER BY stdid,sdate,edate
SELECT * FROM #t
SELECT * FROM #t
WHERE keyid IN ( SELECT keyid FROM #t
WHERE id IN ( SELECT MAX(id) FROM #t GROUP BY STDID))
DROP tb
DROP #t
[解决办法]
稍微改了一下coolingpipe(冷箫轻笛) 写的，如下
select min(a.keyid) as keyid,stdid,sdate,edate from (select * from tb a
where not exists (select 1 from tb where StdID = a.StdID and
(SDate > a.SDate or SDate = a.SDate and EDate > a.EDate))) a
group by a.stdid,a.sdate,a.edate
[解决办法]
select * from table1 a where a.keyid = (select top 1 b.keyid from table1 b where a.stdid=b.stdid order by b.sdate desc,b.edate desc ) order by a.keyid