--假设 recruit 中没有主键,recruitid 是职位select id=identity(int,1,1),* into #t from recruitselect a.recruitid,a.clientid,b.name,a.createdate from #t a,client b where a.clientid=b.clientid and a.id in( select top 10 id from #t where recruitid=a.recruitid order by createdate desc)drop table #t
select t2.name from (select top 10 clientid from recruit order by createdate desc) t1 left outer join client t2 on t1.clientid=t2.clientid
--假设 recruit 中没有主键,recruitid 是职位select id=identity(int,1,1),* into #t from recruitselect a.recruitid,a.clientid,b.name,a.createdate from #t a,client b where a.clientid=b.clientid and a.id in( select top 10 id from #t where recruitid=a.recruitid order by createdate desc)drop table #t
select t2.name from (select top 10 clientid from recruit order by createdate desc) t1 left outer join client t2 on t1.clientid=t2.clientid
问题是从recruit 里去取前10条记录,对应的公司小于10个,一个公司有多个职位
zjcxc(邹建),你上面方法不行,取出的公司有重复
select top 10 * from client where clientid in (select clientid from recruit group by clientid )
zhangzs8896(小二),你的不能按发布职位的时间排序,最新的排在前面
select top 2 * from client where clientid in ( select clientid from ( select clientid,a=max(createdate) from recruit group by clientid ) a )看看这个可以吗
/* client -- 公司表 字段(clientid,name) recruit --招聘职位表 字段(recruitid,clientid,createdate) */ --是不是最近招聘职位并不一定是10个,而是最近的10个公司? create table client(clientid varchar(3),[name] varchar(20)) insert client(clientid,[name]) select '001','天津钢管公司' union all select '002','河北移动公司' union all select '003','包头钢铁学院'create table recruit(recruitid varchar(10),clientid varchar(3) ,createdate datetime) insert recruit(recruitid,clientid,createdate) select '经理','001','2004-08-14' union all select '文员','002','2004-08-15' union all select '文员','001','2004-08-16' union all select '主任','001','2004-08-15' union all select '主任','003','2004-08-12'--我先只取前2个公司的,你改成10看看 select distinct top 2 * from client where clientid in ( select clientid from ( select clientid,a=max(createdate) from recruit group by clientid ) a ) drop table client,recruit --结果 clientid name -------- -------------------- 001 天津钢管公司 002 河北移动公司(所影响的行数为 2 行)
zhangzs8896(小二) ,你上面哪个还是不能排序,不过受你启发,我试了下面这个可以了。select r.clientid, [name]=max([name]) from recruit r,client c where r.clientid=c.clientid group by r.clientid order by max(r.createdate) desc
不明白你的意思 /* client -- 公司表 字段(clientid,name) recruit --招聘职位表 字段(recruitid,clientid,createdate) */ create table client(clientid varchar(3),[name] varchar(20)) insert client(clientid,[name]) select '001','天津钢管公司' union all select '002','河北移动公司' union all select '003','包头钢铁学院'create table recruit(recruitid varchar(10),clientid varchar(3) ,createdate datetime) insert recruit(recruitid,clientid,createdate) select '经理','001','2004-08-14' union all select '文员','002','2004-08-15' union all select '文员','001','2004-08-16' union all select '主任','001','2004-08-15' union all select '主任','003','2004-08-12' union all select '主任','002','2004-08-19' --加一个!'2004-08-19' --我先只取前2个公司的 select distinct top 2 * from client where clientid in ( select clientid from ( select clientid,dt=max(createdate) from recruit group by clientid ) a ) drop table client,recruit--结果 clientid name -------- -------------------- 001 天津钢管公司 002 河北移动公司(所影响的行数为 2 行) 结果不对吗?不知道你到底是加哪了,我觉得没什么问题啊。
from #t a,client b
where a.clientid=b.clientid
and a.id in( select top 10 id from #t where recruitid=a.recruitid order by createdate desc)drop table #t
left outer join client t2 on t1.clientid=t2.clientid
from #t a,client b
where a.clientid=b.clientid
and a.id in( select top 10 id from #t where recruitid=a.recruitid order by createdate desc)drop table #t
可以是 recruitid,clientid,createdate
job1 001 2004-8-19
job2 001 2004-8-19
left outer join client t2 on t1.clientid=t2.clientid
where clientid in
(select clientid from recruit group by clientid )
where clientid in
(
select clientid from
(
select clientid,a=max(createdate) from recruit group by clientid
) a
)看看这个可以吗
client -- 公司表 字段(clientid,name)
recruit --招聘职位表 字段(recruitid,clientid,createdate)
*/
--是不是最近招聘职位并不一定是10个,而是最近的10个公司?
create table client(clientid varchar(3),[name] varchar(20))
insert client(clientid,[name])
select '001','天津钢管公司'
union all
select '002','河北移动公司'
union all
select '003','包头钢铁学院'create table recruit(recruitid varchar(10),clientid varchar(3) ,createdate datetime)
insert recruit(recruitid,clientid,createdate)
select '经理','001','2004-08-14'
union all
select '文员','002','2004-08-15'
union all
select '文员','001','2004-08-16'
union all
select '主任','001','2004-08-15'
union all
select '主任','003','2004-08-12'--我先只取前2个公司的,你改成10看看
select distinct top 2 * from client
where clientid in
(
select clientid from
(
select clientid,a=max(createdate) from recruit group by clientid
) a
)
drop table client,recruit
--结果
clientid name
-------- --------------------
001 天津钢管公司
002 河北移动公司(所影响的行数为 2 行)
[name]=max([name]) from recruit r,client c
where r.clientid=c.clientid
group by r.clientid order by max(r.createdate) desc
因为已经按clientid分组,取的是最大时间。也就是每一个公司取一个
也就是不管你公司最近多少职位,取的都是一个公司id与一个最晚时间。不知道你明白没?
/*
client -- 公司表 字段(clientid,name)
recruit --招聘职位表 字段(recruitid,clientid,createdate)
*/
create table client(clientid varchar(3),[name] varchar(20))
insert client(clientid,[name])
select '001','天津钢管公司'
union all
select '002','河北移动公司'
union all
select '003','包头钢铁学院'create table recruit(recruitid varchar(10),clientid varchar(3) ,createdate datetime)
insert recruit(recruitid,clientid,createdate)
select '经理','001','2004-08-14'
union all
select '文员','002','2004-08-15'
union all
select '文员','001','2004-08-16'
union all
select '主任','001','2004-08-15'
union all
select '主任','003','2004-08-12'
union all
select '主任','002','2004-08-19' --加一个!'2004-08-19'
--我先只取前2个公司的
select distinct top 2 *
from client
where clientid in
(
select clientid from
(
select clientid,dt=max(createdate)
from recruit group by clientid
) a
)
drop table client,recruit--结果
clientid name
-------- --------------------
001 天津钢管公司
002 河北移动公司(所影响的行数为 2 行)
结果不对吗?不知道你到底是加哪了,我觉得没什么问题啊。
误导你了^_^