34,575
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([col_obj_id] int,[col_txt1] varchar(2),[col_txt2] varchar(2),[col_date] datetime)
insert [TB]
select 1,'aa','bb','2010-3-1' union all
select 1,'bb','cc','2010-3-2' union all
select 1,'cc','dd','2010-3-3' union all
select 2,'AA','BB','2010-3-4' union all
select 2,'FF','GG','2010-3-9'
select * from [TB] t where not exists(select 1 from tb where t.[col_obj_id]=[col_obj_id] and t.[col_date]>[col_date])
/*
col_obj_id col_txt1 col_txt2 col_date
----------- -------- -------- -----------------------
1 aa bb 2010-03-01 00:00:00.000
2 AA BB 2010-03-04 00:00:00.000
(2 行受影响)
*/
drop table TB
select
a.col_obj_id,a.col_txt1,b.col_txt2 ,b.col_date
from
(select col_obj_id ,min(col_txt1) as col_txt1 from tb group by col_obj_id)a
join
(select col_obj_id ,max(col_txt2) as col_txt2 ,max(col_date) as col_date from tb group by col_obj_id)b
on
a.col_obj_id=b.col_obj_id
select
a.col_obj_id ,a.col_txt1,
from
(select col_obj_id ,min(col_txt1) as col_txt1 from tb group by col_obj_id)a
join
(select col_obj_id ,max(col_txt2) as col_txt2 ,max(col_date) as col_date from tb group by col_obj_id)b
on
a.col_obj_id=b.col_obj_id