导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

sql语句一条!~

tss1011 2007-12-20 03:03:45

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

感谢
...全文
49 点赞 收藏 5
写回复
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
liangCK 2007-12-20
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 行)

*/
回复
liangCK 2007-12-20
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 2007-12-20
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 行)
*/
回复
tss1011 2007-12-20
? 能写的详细点么?? 就20分了....帮帮忙吧 受累
回复
wzy_love_sly 2007-12-20
select * from table a where not exists(select 1 from table where Deal_id=a.Deal_id and Date>a.date)
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告