表结构如下 number date 8 2009/1/11 2:00 7 2009/1/11 5:00 6 2009/1/11 12:00 5 2009/1/11 18:00 4 2009/1/12 4:00 3 2009/1/12 10:00 2 2009/1/12 12:00 1 2009/1/11 17:00 想得到当天的最早时间与最晚时间的number的差值, 即如
表结构如下 number date 8 2009/1/11 2:00 7 2009/1/11 5:00 6 2009/1/11 12:00 5 2009/1/11 18:00 4 2009/1/12 4:00 3 2009/1/12 10:00 2 2009/1/12 12:00 1 2009/1/11 17:00 想得到当天的最早时间与最晚时间的number的差值, 即如下的结果: 差 2 3 复制代码 代码如下: create table #date ( number int identity(1,1) primary key, date datetime ) insert into #date select '2009/1/11 17:00' insert into #date select '2009/1/12 12:00' insert into #date select '2009/1/12 10:00' insert into #date select '2009/1/12 4:00' insert into #date select '2009/1/11 18:00' insert into #date select '2009/1/11 12:00' insert into #date select '2009/1/11 5:00' insert into #date select '2009/1/11 2:00' select (d2.number-d1.number) number from ( select number,date from #date where date in (select max(date) from #date group by convert(varchar(10),date,120) ) ) d1 , ( select number,date from #date where date in (select min(date) from #date group by convert(varchar(10),date,120) ) ) d2 where convert(varchar(10),d1.date,120)=convert(varchar(10),d2.date,120) number ----------- 2 3