请问,这条sql语句怎么写?每天有若干条记录,找出每天记录里面时间最早和最晚的

Question

字段是t1,t2
记录是
2008-11-01 06:59:00 2008-11-01 07:59:00 
2008-11-01 08:59:00 2008-11-01 09:06:00 
2008-11-01 10:59:00 2008-11-01 11:01:00 2008-11-02 05:59:00 2008-11-02 08:59:00 
2008-11-02 10:59:00 2008-11-02 01:06:00 
2008-11-02 11:59:00 2008-11-02 12:01:00 
现在想找出每天t1最早的是,t2最晚的是

Answer 1

select min(t1),max(t2) from tb

Answer 2

select min(t1),max(t2) from tb group by convert(varchar(10),t1,120),convert(varchar(10),t2,120)

Answer 3

create table tb(t1 datetime , t2 datetime)
insert into tb values('2008-11-01 06:59:00' ,'2008-11-01 07:59:00') 
insert into tb values('2008-11-01 08:59:00' ,'2008-11-01 09:06:00') 
insert into tb values('2008-11-01 10:59:00' ,'2008-11-01 11:01:00') 
insert into tb values('2008-11-02 05:59:00' ,'2008-11-02 08:59:00') 
insert into tb values('2008-11-02 10:59:00' ,'2008-11-02 01:06:00') 
insert into tb values('2008-11-02 11:59:00' ,'2008-11-02 12:01:00') 
goselect isnull(m.t1 , n.t2) [day] , m.t1_min , n.t2_max from
(select convert(varchar(10),t1,120) t1, min(t1) t1_min from tb group by convert(varchar(10),t1,120)) m
full join
(select convert(varchar(10),t2,120) t2, max(t2) t2_max from tb group by convert(varchar(10),t2,120)) n
on m.t1 = n.t2drop table tb/*
day        t1_min                                                 t2_max                                                 
---------- ------------------------------------------------------ ------------------------------------------------------ 
2008-11-01 2008-11-01 06:59:00.000                                2008-11-01 11:01:00.000
2008-11-02 2008-11-02 05:59:00.000                                2008-11-02 12:01:00.000（所影响的行数为 2 行）*/

Answer 4

if object_id('tb')is not null drop table tb
go
create table tb(t1 datetime , t2 datetime)
insert into tb values('2008-11-01 06:59:00' ,'2008-11-01 07:59:00') 
insert into tb values('2008-11-01 08:59:00' ,'2008-11-01 09:06:00') 
insert into tb values('2008-11-01 10:59:00' ,'2008-11-01 11:01:00') 
insert into tb values('2008-11-02 05:59:00' ,'2008-11-02 08:59:00') 
insert into tb values('2008-11-02 10:59:00' ,'2008-11-02 01:06:00') 
insert into tb values('2008-11-02 11:59:00' ,'2008-11-02 12:01:00') 
go
select min(t1),max(t2) from tb group by convert(varchar(10),t1,120),convert(varchar(10),t2,120)
/*(影響 1 個資料列)                                                                                                              
------------------------------------------------------ ------------------------------------------------------ 
2008-11-01 06:59:00.000                                2008-11-01 11:01:00.000
2008-11-02 05:59:00.000                                2008-11-02 12:01:00.000(影響 2 個資料列)
*/

Answer 5

select min(t1),max(t2) from @t group by convert(nvarchar(10),t1,112)