sql语句一条!~

create table tb(

event_id int,

[user_id] int,

[date] datetime,

deal_id int,

event_type varchar(20)

)



insert tb select 36,    1,    '12/20/2007 10:19:00 AM',    52    ,'Update'

insert tb select 37 ,   1 ,   '12/20/2007 10:20:00 AM' ,   52   , 'Update'

insert tb select 42  ,  1  ,  '12/13/2007 1:55:00 PM'   ,54    ,'Update'

insert tb select 38   , 1   , '12/20/2007 10:20:00 AM'   , 54  ,  'Update'

insert tb select 39    ,1    ,'12/20/2007 10:21:00 AM'    ,55 ,   'Update'

insert tb select 43    ,1    ,'12/13/2007 1:55:00 PM'    ,56 ,   'Update'

insert tb select 46    ,1    ,'12/14/2007 2:23:00 PM'    ,57,    'NewDeal'



select a.* from tb a,(select min([date]) [date],deal_id from tb group by deal_id) b

where a.[date]=b.[date] and a.deal_id=b.deal_id



drop table tb



/*

event_id    user_id     date                                                   deal_id     event_type           

----------- ----------- ------------------------------------------------------ ----------- -------------------- 

36          1           2007-12-20 10:19:00.000                                52          Update

42          1           2007-12-13 13:55:00.000                                54          Update

39          1           2007-12-20 10:21:00.000                                55          Update

43          1           2007-12-13 13:55:00.000                                56          Update

46          1           2007-12-14 14:23:00.000                                57          NewDeal



（所影响的行数为 5 行）



*/

create table tb(

event_id int,

[user_id] int,

[date] datetime,

deal_id int,

event_type varchar(20)

)



insert tb select 36,    1,    '12/20/2007 10:19:00 AM',    52    ,'Update'

insert tb select 37 ,   1 ,   '12/20/2007 10:20:00 AM' ,   52   , 'Update'

insert tb select 42  ,  1  ,  '12/13/2007 1:55:00 PM'   ,54    ,'Update'

insert tb select 38   , 1   , '12/20/2007 10:20:00 AM'   , 54  ,  'Update'

insert tb select 39    ,1    ,'12/20/2007 10:21:00 AM'    ,55 ,   'Update'

insert tb select 43    ,1    ,'12/13/2007 1:55:00 PM'    ,56 ,   'Update'

insert tb select 46    ,1    ,'12/14/2007 2:23:00 PM'    ,57,    'NewDeal'



select * from tb a,(select min([date]) [date],deal_id from tb group by deal_id) b

where a.[date]=b.[date] and a.deal_id=b.deal_id



drop table tb



/*

event_id    user_id     date                                                   deal_id     event_type           date                                                   deal_id     

----------- ----------- ------------------------------------------------------ ----------- -------------------- ------------------------------------------------------ ----------- 

36          1           2007-12-20 10:19:00.000                                52          Update               2007-12-20 10:19:00.000                                52

42          1           2007-12-13 13:55:00.000                                54          Update               2007-12-13 13:55:00.000                                54

39          1           2007-12-20 10:21:00.000                                55          Update               2007-12-20 10:21:00.000                                55

43          1           2007-12-13 13:55:00.000                                56          Update               2007-12-13 13:55:00.000                                56

46          1           2007-12-14 14:23:00.000                                57          NewDeal              2007-12-14 14:23:00.000                                57



（所影响的行数为 5 行）

*/

create table tb(

event_id int,

[user_id] int,

[date] datetime,

deal_id int,

event_type varchar(20)

)



insert tb select 36,    1,    '12/20/2007 10:19:00 AM',    52    ,'Update'

insert tb select 37 ,   1 ,   '12/20/2007 10:20:00 AM' ,   52   , 'Update'

insert tb select 42  ,  1  ,  '12/13/2007 1:55:00 PM'   ,54    ,'Update'

insert tb select 38   , 1   , '12/20/2007 10:20:00 AM'   , 54  ,  'Update'

insert tb select 39    ,1    ,'12/20/2007 10:21:00 AM'    ,55 ,   'Update'

insert tb select 43    ,1    ,'12/13/2007 1:55:00 PM'    ,56 ,   'Update'

insert tb select 46    ,1    ,'12/14/2007 2:23:00 PM'    ,57,    'NewDeal'



select * from tb a where [date] in(select min(date) from tb where a.deal_id=deal_id)



drop table tb



/*

event_id    user_id     date                                                   deal_id     event_type           

----------- ----------- ------------------------------------------------------ ----------- -------------------- 

36          1           2007-12-20 10:19:00.000                                52          Update

42          1           2007-12-13 13:55:00.000                                54          Update

39          1           2007-12-20 10:21:00.000                                55          Update

43          1           2007-12-13 13:55:00.000                                56          Update

46          1           2007-12-14 14:23:00.000                                57          NewDeal



（所影响的行数为 5 行）

*/