sql语句一条!~

[tss1011]

event_id user_id  Date                Deal_id  event_type
36	1	12/20/2007 10:19:00 AM	52	Update
37	1	12/20/2007 10:20:00 AM	52	Update
42	1	12/13/2007 1:55:00 PM	54	Update
38	1	12/20/2007 10:20:00 AM	54	Update
39	1	12/20/2007 10:21:00 AM	55	Update
43	1	12/13/2007 1:55:00 PM	56	Update
46	1	12/14/2007 2:23:00 PM	57	NewDeal

需求: 选出每个Deal中时间最早的一条记录
结果应该是

event_id user_id  Date                Deal_id  event_type
36	1	12/20/2007 10:19:00 AM	52	Update
42	1	12/13/2007 1:55:00 PM	54	Update
39	1	12/20/2007 10:21:00 AM	55	Update
43	1	12/13/2007 1:55:00 PM	56	Update
46	1	12/14/2007 2:23:00 PM	57	NewDeal

感谢

[wzy_love_sly]

select * from table a where not exists(select 1 from table where Deal_id=a.Deal_id and Date>a.date)

[tss1011]

? 能写的详细点么?? 就20分了....帮帮忙吧 受累

[liangCK]

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 行）
*/

[liangCK]

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 行）
*/

[liangCK]

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 行）

*/