主页
基础类
应用实例
新技术前沿

select问题。

javaname6 2007-12-28 12:12:14

表A：里有cid,aname 两个字段.
表B: 里有jobid,cid,jobname 三个字段。 cid可能重复多个。

现在我想这样取：取表a里5最后5条记录，如果这5条记录在b里有对应的信息就把对应的jobname信息显示出来，没有就显示jobname为空。如果在b里有多个对应的记录，那么就取最后一条和他对应的。

这个是一个职位发布的，就是显示5条最新注册的企业及发布的职位，如果没有发布职位，光显示公司名称就行了。

...全文

14 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

qiule 2007-12-29

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

回复赞

gracexu 2007-12-29

CXMCXM好是好，但是假设结果里要增加字段jobid，读上去就。。。

回复赞

pt1314917 2007-12-28



表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

回复赞

dobear_0922 2007-12-28

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

回复赞

simonhehe 2007-12-28



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

回复赞

playwarcraft 2007-12-28

select T1.cid,T1.aname,T2.jobid,T2.jobname
from
(select top 5 cid,aname from A order by cid desc) T1
left join
(select X.* from B X
inner join
(select cid,max(jobid) as jobid from B group by cid) Y
on X.jobid=Y.jobid) T2
on T1.cid=T2.cid

回复赞

cxmcxm 2007-12-28

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

回复赞

-狙击手- 2007-12-28

表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

回复赞

victorcai2006 2007-12-28

牛X的老龟～～～～
貌似没有老龟解决不了的问题

回复赞

gracexu 2007-12-28

老乌龟很喜欢用子查询啊，我喜欢你的写法。

回复赞

dawugui 2007-12-28

--假设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

回复赞

dawugui 2007-12-28

--假设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

回复赞

gracexu 2007-12-28

一楼的写法是对的，我也提供一种写法，差别只是谁先和谁联接的问题。

select a.cid, a.aname,b.jobid, b.jobname
from a inner join
（select top 5 a.cid, max(b.jobid) as jobid
from a left join b on a.cid=b.cid group by a.cid order by a.cid desc ) as T
on a.cid=t.cid
left join b on b.jobid=t.jobid

回复赞

tianhama 2007-12-28

create table A (cid int identity(1,1),aname varchar(10))

insert into A select 'aa'
union all select 'bb'
union all select 'cc'
union all select 'dd'
union all select 'ee'
union all select 'ff'
union all select 'gg'
union all select 'hh'

create table B (jobid int identity(1,1),cid int,jobname varchar(10))

insert into B select 1,'a'
union all select 2,'a'
union all select 3,'a'
union all select 4,'a'
union all select 5,'a'
union all select 6,'a'
union all select 7,'a'
union all select 8,'a'
union all select 8,'b'

select a.cid as 企业编号,a.aname as 企业名称,b.jobid as 职位编号,b.jobname as 职位名称 from A a left join B b
on a.cid = b.cid
where a.cid in (select top 5 cid from A order by cid desc)
and
b.jobid in (select max(jobid) from B group by cid )

结果集如下：
企业编号企业名称职位编号职位名称
4 dd 4 a
5 ee 5 a
6 ff 6 a
7 gg 7 a
8 hh 9 b

回复赞

发动态

发帖子

MS-SQL Server

创建于2007-09-28

加入

3.2w+

社区成员

25.4w+

社区内容

MS-SQL Server相关内容讨论专区

申请成为版主

社区公告

暂无公告