select c_id,Jobid,publishdate from job a where not exists (select 1 from job where a.c_id=c_id and publishdate>a.publishdate)
按你的思路,我是sql 2005,嵌套语句没通过。
这是我的实际语句 select C_Id form(select top 90 C_Id,Convert(DateTime,PublishDate) from Job where Convert(DateTime,ClosingDate)>Convert(DateTime,'2012-12-25') group by C_Id,Publishdate order by Convert(DateTime,PublishDate) Desc) 红色部分本来语法没有问题,只是查询出来的记录多了,因为他只剔除C_Id,PublishDate两个字段同时不一样的记录,明显冗余了。按你的思路,加了嵌套,语法报错,“关键字 'order' 附近有语法错误。"
忘记了一个点,这个试试:SELECT JobName FROM ( SELECT TOP 100 PERCENT JobName , PublishDate FROM Job GROUP BY JobName , PublishDate ORDER BY PublishDate DESC ) a
步骤1:把日期列创建聚集索引,并降序排列,这个还是比较合理的。 步骤2:select discint JobName from Job
还是不行,现在语句如下:select C_Id form(select top 100 PERCENT C_Id,Convert(DateTime,PublishDate) from Job where Convert(DateTime,ClosingDate)>Convert(DateTime,'2012-12-25') group by C_Id,Publishdate order by Convert(DateTime,PublishDate) Desc) a还是不行,报错"关键字 'order' 附近有语法错误。"。语句的红色部分是可以查询的。 而且你的语句最后那个"a" 是要得还是不要的?不过我试了都报错。
如果你的表没有聚集索引,那就在PublishDate 创建一个聚集索引,顺序为desc,然后直接select discint JobName from Job
SELECT C_id, PubulishDate FROM Joba WHERE EXISTS ( SELECT 1 FROM ( SELECT C_id, MAX(PubulishDate) AS PubulishDate FROM Job GROUP BY C_id ) t WHERE C_id = a.C_id AND t.PubulishDate = a.PubulishDate ) ORDER BY PubulishDate DESC
额...上面少打了个空格 SELECT C_id, PubulishDate FROM Job a WHERE EXISTS ( SELECT 1 FROM ( SELECT C_id, MAX(PubulishDate) AS PubulishDate FROM Job GROUP BY C_id ) t WHERE t.C_id = a.C_id AND t.PubulishDate = a.PubulishDate ) ORDER BY PubulishDate DESC
select * from (select jobid,c_id,c_name,publishdate,row_number() over(partiton by c_id order by publishdate desc) as rn) p where rn=1
感谢"DBA_Huangzj”版主,及楼上各位的解答。目前我的问题已经解决,用的是下面这个思路。select a.C_Id from Job a ,(select max(JobId) JobId,C_Id from Job group by C_Id ) b where a.JobId=b.JobId order by a.PublishDate
FROM ( SELECT JobName ,
PublishDate
FROM Job
GROUP BY JobName ,
PublishDate
ORDER BY PublishDate DESC
) a
select jobname,publishdate from job a where not exists (select 1 from job where publishdate>a.publishdate)
C_id = 公司名称
JobID = 职位名称
PubulishDate = 发布时间所以,查询语句中是要求找公司名字。我原文写的“JobName”,是笔误,其实应该为“C_Name”。
select c_id,Jobid,publishdate from job a where not exists (select 1 from job where a.c_id=c_id and publishdate>a.publishdate)
按你的思路,我是sql 2005,嵌套语句没通过。
select C_Id form(select top 90 C_Id,Convert(DateTime,PublishDate) from Job where Convert(DateTime,ClosingDate)>Convert(DateTime,'2012-12-25') group by C_Id,Publishdate order by Convert(DateTime,PublishDate) Desc) 红色部分本来语法没有问题,只是查询出来的记录多了,因为他只剔除C_Id,PublishDate两个字段同时不一样的记录,明显冗余了。按你的思路,加了嵌套,语法报错,“关键字 'order' 附近有语法错误。"
FROM ( SELECT TOP 100 PERCENT
JobName ,
PublishDate
FROM Job
GROUP BY JobName ,
PublishDate
ORDER BY PublishDate DESC
) a
步骤2:select discint JobName from Job
而且你的语句最后那个"a" 是要得还是不要的?不过我试了都报错。
SELECT C_id, PubulishDate
FROM Joba
WHERE EXISTS ( SELECT 1
FROM ( SELECT C_id, MAX(PubulishDate) AS PubulishDate
FROM Job
GROUP BY C_id
) t
WHERE C_id = a.C_id AND t.PubulishDate = a.PubulishDate )
ORDER BY PubulishDate DESC
SELECT C_id, PubulishDate
FROM Job a
WHERE EXISTS ( SELECT 1
FROM ( SELECT C_id, MAX(PubulishDate) AS PubulishDate
FROM Job
GROUP BY C_id
) t
WHERE t.C_id = a.C_id AND t.PubulishDate = a.PubulishDate )
ORDER BY PubulishDate DESC
where rn=1