有关于简单sql的问题

Question

SELECT t.*,c.nvcName FROM 
(SELECT ri.iUserID,rr.ResumeID,rr.CompanyID,rr.ReadDate 
FROM ps_s_ResumeRead rr,ps_s_ResumeInfo ri 
WHERE RR.ResumeID = ri.iID 
AND ri.iUserID = 359025)
t,et_s_Comp c
WHERE t.CompanyID = c.iID 
GROUP BY t.iUserID,t.ResumeID,t.CompanyID,t.ReadDate,c.nvcName
ORDER BY t.ReadDate DESC请教大家有关于sql的一个问题,我想取出ReadDate字段内时间最新的一条记录,不能用top因为会有多个CompanyID

Answer 1

select a.* from ps_s_ResumeRead a where exists(select 1 from ps_s_ResumeRead where 
CompanyID=a.CompanyID and ReadDate<a.ReadDate)

Answer 2

try
SELECT t.*,c.nvcName FROM 
(SELECT ri.iUserID,rr.ResumeID,rr.CompanyID,rr.ReadDate 
    FROM ps_s_ResumeRead rr,ps_s_ResumeInfo ri 
    WHERE RR.ResumeID = ri.iID 
        AND ri.iUserID = 359025
AND NOT EXISTS(
  SELECT 1 FROM ps_s_ResumeRead a,ps_s_ResumeInfo b 
    WHERE a.ResumeID = b.iID 
        AND a.iUserID = 359025
          AND a.ReadDate>rr.ReadDate)
)t,
et_s_Comp c
WHERE t.CompanyID = c.iID 
GROUP BY t.iUserID,t.ResumeID,t.CompanyID,t.ReadDate,c.nvcName
ORDER BY t.ReadDate DESC

Answer 3

--try
SELECT t.*,c.nvcName FROM 
(SELECT ri.iUserID,rr.ResumeID,rr.CompanyID,rr.ReadDate 
    FROM ps_s_ResumeRead rr,ps_s_ResumeInfo ri 
    WHERE RR.ResumeID = ri.iID
and not exists(select 1 from ps_s_ResumeRead where ResumeID=rr.ResumeID and ReadDate > rr.ReadDate ) 
        AND ri.iUserID = 359025)
    t,et_s_Comp c
WHERE t.CompanyID = c.iID

Answer 4

if not object_id('[ps_s_ResumeRead]') is  null 
drop table ps_s_ResumeRead
go
create table [ps_s_ResumeRead]([IuserID] int,[ReSumeID] int,
companyID INT,ReadDATE DATETIME ,NVCNAME CHAR(200))
go
insert [ps_s_ResumeRead]
select 309025,357640,26685,'2010-02-11 20:25:02.500','heau' union all
select 309025,357640,26685,'2010-02-11 20:26:02.500','heau' union all
select 309025,357641,26685,'2010-02-11 20:25:02.500','heau' union all
select 309025,357641,26687,'2010-02-11 20:23:02.500','heau' union all
select 309025,357641,26687,'2010-02-11 20:24:02.500','heau' go
select a.* from ps_s_ResumeRead a 
where a.ReadDate=(select MAX(b.ReadDate) from ps_s_ResumeRead b where 
b.CompanyID=a.CompanyID )
go
select a.* from ps_s_ResumeRead a where exists(select 1 from ps_s_ResumeRead where 
CompanyID=a.CompanyID and ReadDate<a.ReadDate)

Answer 5

IuserID ReSumeID companyID ReadDATE NVCNAME
309025 357641 26687 2010-02-11 20:24:02.500 heau                                                                                                                                                                                                    
309025 357640 26685 2010-02-11 20:26:02.500 heau