select a.personid,personname,status
from person a ,status b,
(select personid,max(dt) dt from status group by personid,convert(char(10),dt,120) c
where a.personid=b.personid and b.personid=c.personid and b.dt=c.dt
from person a ,status b,
(select personid,max(dt) dt from status group by personid,convert(char(10),dt,120) c
where a.personid=b.personid and b.personid=c.personid and b.dt=c.dt
请大力兄再出一把力:P
好象有点矛盾
where dt=(select max(dt) from status where personid=a.personid and convert(char(10),dt,120)=convert(char(10),a.dt,120))
下面是全部的
select a.personid,personname,status
from person a ,status b where a.personid=b.personid
from person a ,status b,
(select personid,max(dt) dt from status group by personid,convert(char(10),dt,120) c
where a.personid=b.personid and b.personid=c.personid and b.dt=c.dt
insert #person values(1,'zhangsan')
insert #person values(2,'lisi')create table #status(personid int,status int,dt datetime)
insert #status values(1,1,'2003-5-1 18:00:00')
insert #status values(1,2,'2003-5-1 19:00:00')
insert #status values(1,3,'2003-5-2 10:00:00')
insert #status values(2,1,'2003-5-1 12:00:00')select * from #status a where dt=(select max(dt) from #status where personid=a.personid group by CONVERT(varchar(10),dt,120) having CONVERT(varchar(10),dt,120)=CONVERT(varchar(10),a.dt,120))
go
drop table #person ,#status