accno opdate optime
……
12 2011-03-01 12:01:00
12 2011-03-01 12:00:00
12 2011-03-04 11:00:00
12 2011-03-04 11:01:00
……
怎么能让结果显示为
accno opdate optime
……
12 2011-03-01 12:00:00
……
……
12 2011-03-01 12:01:00
12 2011-03-01 12:00:00
12 2011-03-04 11:00:00
12 2011-03-04 11:01:00
……
怎么能让结果显示为
accno opdate optime
……
12 2011-03-01 12:00:00
……
accno,min(opdate) as opdate,min(optime) as optime
from tablename group by accno
where not exists(select 1 from tb where accno=t.accno and opdate<t.opdate or (opdate=t.opdate and optime<t.optime))
(select min(opdate+' '+optime) from tablename where accno=t.accno)
试试
create table #tb(accno int, opdate char(10), optime char(8))
insert into #tb
select 12,'2011-03-01', '12:01:00' union all
select 12 ,'2011-03-01' ,'12:00:00' union all
select 12, '2011-03-04' ,'11:00:00' union all
select 12, '2011-03-04' ,'11:01:00'select top 1 * from #tb order by opdate,optime
accno opdate optime
----------- ---------- --------
12 2011-03-01 12:00:00(1 行受影响)
where not exists(select 1 from tb where accno=t.accno and datediff(s,opdate+' '+optime,t.opdate+' '+t.optime)>0)