有两个表,A表存放用户基本资料,主键UID,有10来个字段,无重复记录。
B表存放用户的登陆信息,每次登陆都会新增一条记录,外键是UID。
现在希望能联合查出A表的所有记录和B表对应UID的最新一条记录,以前我是这么做的select a.*,
n1=(select n1 from b where uid=a.uid order by createtime desc),
n2=(select n2 from b where uid=a.uid order by createtime desc),
n3=(select n3 from b where uid=a.uid order by createtime desc),
n4=(select n4 from b where uid=a.uid order by createtime desc),
n5=(select n5 from b where uid=a.uid order by createtime desc)
from a但是感觉太麻烦了,而且效率也不高,不知还有没有更好的办法
B表存放用户的登陆信息,每次登陆都会新增一条记录,外键是UID。
现在希望能联合查出A表的所有记录和B表对应UID的最新一条记录,以前我是这么做的select a.*,
n1=(select n1 from b where uid=a.uid order by createtime desc),
n2=(select n2 from b where uid=a.uid order by createtime desc),
n3=(select n3 from b where uid=a.uid order by createtime desc),
n4=(select n4 from b where uid=a.uid order by createtime desc),
n5=(select n5 from b where uid=a.uid order by createtime desc)
from a但是感觉太麻烦了,而且效率也不高,不知还有没有更好的办法
n.n1,
n.n2,
n.n3,
n.n4,
n.n5
from a m,b n
where m.uid=n.uid
and not exists(select 1
from b
where uid=n.uid and createtime>n.createtime)
他应该忘记了top 1
--效率相对较高的写法
select a.*, b.*
from a
cross apply (select top (1) n1,n2,n3,n4,n5 from b where uid = a.uid order by createtime desc) b
记得结贴哦
select top(1) a.uid,b.n1,b.n2,b.n3,b.n4,b.n5
from a join b on a.uid=b.uid
group by a.uid
order by b.createtime desc
--
select a.*,b.n1,b.n2,b.n3,b.n4,b.n5
from a cross apply(select top(1) n1,n2,n3,n4,n5 from b where a.uid=b.uid order by createtime desc) as b
楼主试试
感谢啊,方法一用不了吧,那几个不包含在聚合字段里,会报错啊
方法二不错,和 coleling 的方法一致,以前没有用过,感谢大家,这次已经结贴了没法加分呢!