34,838
社区成员




表A:里有cid,aname 两个字段.
表B: 里有jobid,cid,jobname 三个字段。 cid可能重复多个。
现在我想这样取:取表a里5最后5条记录,如果这5条记录在b里有对应的信息就把对应的jobname信息显示出来,没有就显示jobname为空。如果在b里有多个对应的记录,那么就取最后一条和他对应的。
------------------------------------------------------------
select isnull(jobname,'') from b k1,
(select top 5 cid from a order by cid desc)a
where not exists(select 1 from b where cid=k1.cid and jobid>k1.jobid) and k1.cid=a.cid
select T.cid,jobid = min(jobid),jobname = isnull(b.jobname,'')
from (select top 5 cid from a order by cid desc) T
left join b on T.cid = b.cid
group by T.cid
select a.cid,jobid = min(jobid),jobname = isnull(b.jobname,'')
from (select top 5 cid from a order by cid desc)a left join b on a.cid = b.cid
group by a.cid
select top 5 cid,aname,jobname=(select top 1 jobname from B where cid=a.cid order by jobid desc) from a
order by cid desc
表A:里有cid,aname 两个字段.
表B: 里有jobid,cid,jobname 三个字段。 cid可能重复多个。
select a.*,isnull(b.jobname,'') as jobname
from (select top 5 * from tablea order by cid desc) a
left join (select * from tableb bb where not exists(select 1 from tableb where bb.cid = cid and jobid > bb.jobid)) b
on a.cid = b.cid
--假设cid越大表示越后面.
--假设jobid越大表示越后面
--上面看漏了条件,B有多条.
select m.* , isnull(n.jobname,'') jobname from
(select top 5 * from A order by cid desc) m
left join
(select t.* from B t where jobid = (select max(jobid) from B where cid = t.cid)) n
on m.cid = n.cid
--假设cid越大表示越后面.
select m.* , isnull(b.jobname,'') jobname from
(select top 5 * from A order by cid desc) m
left join B on m.cid = b.cid