select * from 一张表 a where not exists (select 1 from 一张表 where date(rtime)=date(a.rtime) and rtime<a.rtime) or not exists (select 1 from 一张表 where date(rtime)=date(a.rtime) and rtime>a.rtime)
如果是按照time排序,将下面命令所有的rid换成time字段 create table #tablea (rid bigint,id bigint,date datetime) insert into #tablea select 1,1,'2013-01-01' union all select 2,1,'2013-01-02' union all select 3,1,'2013-01-03' union all select 4,1,'2013-01-04' union all select 5,2,'2013-01-02' union all select 6,2,'2013-01-03' union all select 7,2,'2013-01-04'-- SQL select a.* from #tablea a inner join ( select id,MAX(rid) rid1,MIN(rid) rid2 from #tablea group by id) b on a.rid=b.rid1 or a.rid=b.rid2
若只有一天的记录,则sql语句可以写成: select id,min(rtime),max(rtime) from t_record group by id
from 一张表 a
where not exists (select 1 from 一张表 where date(rtime)=date(a.rtime) and rtime<a.rtime)
or not exists (select 1 from 一张表 where date(rtime)=date(a.rtime) and rtime>a.rtime)
create table #tablea (rid bigint,id bigint,date datetime)
insert into #tablea
select 1,1,'2013-01-01'
union all
select 2,1,'2013-01-02'
union all
select 3,1,'2013-01-03'
union all
select 4,1,'2013-01-04'
union all
select 5,2,'2013-01-02'
union all
select 6,2,'2013-01-03'
union all
select 7,2,'2013-01-04'-- SQL
select a.* from #tablea a inner join (
select id,MAX(rid) rid1,MIN(rid) rid2 from #tablea group by id) b
on a.rid=b.rid1 or a.rid=b.rid2
select id,min(rtime),max(rtime) from t_record group by id