求一查询语句!

[Jonly]

--测试数据
create table tblN(ID int,AID int,BID,CID,DateS DateTime)
insert tblN select 1,1,11,111,'2004-4-1'
union all select 2,2,22,222,'2004-4-1'
union all select 3,3,33,333,'2004-4-1'
union all select 4,1,11,111,'2004-4-5'
union all select 5,1,11,111,'2004-4-7'
union all select 6,3,33,333,'2004-4-5'

go


想得到两张不同的表：
一：
id AID BID CID DateS
2 2 22 222 '2004-4-1'
5 1 11 111 '2004-4-7'
6 3 33 333 '2004-4-5'
就是在记录中AID，BID，CID相同的情况下只取DateS日期最后的记录

二：
id AID BID CID DateS
1 1 11 111 '2004-4-1'
3 3 33 333 '2004-4-1'
4 1 11 111 '2004-4-5'
就是在记录中AID，BID，CID相同的情况下只取DateS日期小于最后的记录

--------------

id 字段是主键而且是自增字段

不知道我有没说清楚。
敬请各位帮忙！谢谢!

[pisces007]

1.
select L.id,R.aid,R.bid,R.cid,R.dates
from tblN L join
(select aid,bid,cid,max(dates) as dates from tblN group by aid,bid,cid) R
on L.aid=R.aid and L.bid=R.bid and L.cid=R.cid and L.dates=R.dates
order by L.id

[loverpyh]

select m.id,t.AID, t.BID, t.CID from (select AID, BID, CID, max(DateS) as DateS from table group by AID, BID, CID) t left outer join table m on t.AID=m.AID AND t.BID=m.BID AND t.CID=m.CID

[loverpyh]

select m.id,t.AID, t.BID, t.CID from (select AID, BID, CID, max(DateS) as DateS from table group by AID, BID, CID) t left outer join table m on t.AID=m.AID AND t.BID=m.BID AND t.CID=m.CID and t.dates=m.dates order by t.id

[pisces007]

2.
select L.id,R.aid,R.bid,R.cid,R.dates
from tblN L join
(select aid,bid,cid,max(dates) as dates from tblN group by aid,bid,cid) R
on L.aid=R.aid and L.bid=R.bid and L.cid=R.cid and L.dates<R.dates
order by L.id

[pisces007]

2。改一下
select L.id,L.aid,L.bid,L.cid,L.dates
from tblN L join
(select aid,bid,cid,max(dates) as dates from tblN group by aid,bid,cid) R
on L.aid=R.aid and L.bid=R.bid and L.cid=R.cid and L.dates<R.dates
order by L.id

测试正确

[paoluo]

1.

select A.* from tblN A
inner join (Select AID,BID,CID,Max(DateS) As DateS from tblN Group By AID,BID,CID) B
On A.AID=B.AID AND A.BID=B.BID AND A.CID=B.CID AND A.DateS=B.DateS
Order By A.ID

[paoluo]

2.


select A.* from tblN A
inner join (Select AID,BID,CID,Max(DateS) As DateS from tblN Group By AID,BID,CID) B
On A.AID=B.AID AND A.BID=B.BID AND A.CID=B.CID AND A.DateS<B.DateS
Order By A.ID