从数据库中取出某一天记录的最大和最小

师姐问了这个问题，就顺便想了想，这里是转来的，这个方法应该比较靠谱：

http://club.topsage.com/thread-493697-1-1.html

 

 

表结构如下

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

1. create table #date
2. (
3.   number int identity(1,1) primary key,
4.   date datetime
5. )
6. insert into #date select '2009/1/11 17:00'
7. insert into #date select '2009/1/12 12:00'
8. insert into #date select '2009/1/12 10:00'
9. insert into #date select '2009/1/12 4:00'
10. insert into #date select '2009/1/11 18:00'
11. insert into #date select '2009/1/11 12:00'
12. insert into #date select '2009/1/11 5:00'
13. insert into #date select '2009/1/11 2:00'
15. select (d2.number-d1.number) number
16. from
17. (
18. select number,date from #date where date in
19. (select max(date) from #date group by convert(varchar(10),date,120) )
20. ) d1
21. ,
22. (
23. select number,date from #date where date in
24. (select min(date) from #date group by convert(varchar(10),date,120) )
25. ) d2
26. where convert(varchar(10),d1.date,120)=convert(varchar(10),d2.date,120)

复制代码

number
-----------
2
3