表A
username tname
张三 aaa
李四 bbb表B
username datetime
张三 2008-8-7
张三 2004-9-7
李四 2009-8-7现在想联合起来只取表A里面的记录,并按表B里的datetime降序排序
select tname from 表A,表B where 表A.username=表B.username order by datetime desc但这样显示的记录张三的tname是显示了两条,怎么才能让他只显示一条?
username tname
张三 aaa
李四 bbb表B
username datetime
张三 2008-8-7
张三 2004-9-7
李四 2009-8-7现在想联合起来只取表A里面的记录,并按表B里的datetime降序排序
select tname from 表A,表B where 表A.username=表B.username order by datetime desc但这样显示的记录张三的tname是显示了两条,怎么才能让他只显示一条?
and not exists(select 1 from 表B where username=b.username and [datetime]>b.[datetime])
order by datetime desc
--or
select a.tname from 表A a,表B b where a.username=b.username
and not exists(select 1 from 表B where username=b.username and [datetime]<b.[datetime])
order by datetime desc
where not exists (select 1 from B as C on C.username=B.username and C.[datetime]>B.[datetime]
)
and not exists(select 1 from 表B where username=b.username and [datetime]>b.[datetime])
order by datetime desc
drop table tbbcreate table tbb
(username varchar(100),
date datetime
)if object_id('tba') is not null
drop table tbacreate table tba
(username varchar(100),
tname varchar(100)
)
insert into tbb values('张三','2008-8-7')
insert into tbb values('张三','2004-9-7')
insert into tbb values('李四','2004-9-7')
insert into tbb values('李四','2009-10-7')insert into tba values('张三','aaa')
insert into tba values('李四','bbb');
with d as (select a.username ,a.tname,b.date
from tba as a inner join tbb as b on a.username=b.username)
select d.* from d,d as e
where d.username=e.username and d.date<e.date张三 aaa 2004-09-07 00:00:00.000
李四 bbb 2004-09-07 00:00:00.000