两个表a,b
a结构内容
aid aname atime
1 aa 2008-08-18 11:00:00
2 bb 2008-08-18 11:00:10
3 cc 2008-08-18 11:00:20
a结构内容
id aid bname btime
1 1 qq 2008-08-18 12:00:00
2 1 ww 2008-08-18 12:00:10
3 2 ee 2008-08-18 11:00:15需要的结果
aid aname bname atime btime
1 aa ww 2008-08-18 11:00:00 2008-08-18 12:00:10
3 cc 2008-08-18 11:00:20
2 bb ee 2008-08-18 11:00:10 2008-08-18 11:00:15也就是,先按btime排序,然后在按atime排序,a.aid=b.aid ,但a里记录,b里可能没有。感谢!
a结构内容
aid aname atime
1 aa 2008-08-18 11:00:00
2 bb 2008-08-18 11:00:10
3 cc 2008-08-18 11:00:20
a结构内容
id aid bname btime
1 1 qq 2008-08-18 12:00:00
2 1 ww 2008-08-18 12:00:10
3 2 ee 2008-08-18 11:00:15需要的结果
aid aname bname atime btime
1 aa ww 2008-08-18 11:00:00 2008-08-18 12:00:10
3 cc 2008-08-18 11:00:20
2 bb ee 2008-08-18 11:00:10 2008-08-18 11:00:15也就是,先按btime排序,然后在按atime排序,a.aid=b.aid ,但a里记录,b里可能没有。感谢!
select aid,aname atime from
a left join b
on a.aid = b.bid
order by b.btime,a.atime
FROM A
LEFT JOIN B ON A.AID=B.AID
ORDER BY A.ATIME,B.BTIME
FROM A LEFT JOIN B ON A.AID=B.AID
ORDER BY BTIME DESC ,ATIME
select
a.aid,a.aname,x.bname,a.atime,x.btime
from a
outer apply (
select top 1 * from b
where aid = a.aid
order by btime desc
) as x
order by isnull(x.btime,a.atime) desc
FROM A LEFT JOIN B ON A.AID=B.AID
ORDER BY ISNULL(BTIME,ATIME) DESC ,ATIME
declare @t1 table(aid int, aname varchar(10), atime datetime)
insert into @t1
select 1, 'aa', '2008-08-18 11:00:00' union all
select 2, 'bb', '2008-08-18 11:00:10' union all
select 3, 'cc', '2008-08-18 11:00:20'
declare @t2 table(id int, aid int, bname varchar(10),btime datetime)
insert into @t2
select 1, 1, 'qq' , '2008-08-18 12:00:00' union all
select 2, 1, 'ww' , '2008-08-18 12:00:10' union all
select 3, 2, 'ee' , '2008-08-18 11:00:15'
;with cte as
(
select a.aid,a.aname,b.bname,a.atime,b.btime from @t1 a left join @t2 b on a.aid = b.aid
)
select * from cte a where not exists(select 1 from cte where a.aid = aid and a.atime = atime and a.btime<btime)
(
aid int
,aname nvarchar(20)
,atime datetime
);
declare @b table
(
id int
,aid int
,bname nvarchar(20)
,btime datetime
);insert into @a
select 1,'aa','2008-08-18 11:00:00'
union all
select 2,'bb','2008-08-18 11:00:10'
union all
select 3,'cc','2008-08-18 11:00:20' ;
insert into @b
select 1,1,'qq','2008-08-18 12:00:00'
union all
select 2,1,'ww','2008-08-18 12:00:10'
union all
select 3,2,'ee','2008-08-18 11:00:15' ;select t1.aid
,t1.aname
,t2.bname
,t1.atime
,t2.btime
from @a t1
left join @b t2 on t1.aid=t2.aid
where not exists(select 1 from @b where btime>t2.btime and aid=t2.aid)
order by t2.btime desc,t1.atime desc
select A.aid,aname,bname,atime,btime
from A left join B
on A.aid=B.aid
order by btime DESC,atime desc
select a.aid,aname,bname,atime,btime
from #t1 a left join (
select * from #t2 c
where not exists (select 1 from #t2 d where c.aid = d.aid and c.btime < d.btime)) b
on a.aid = b.aid