--> 测试数据：[test]
if object_id('[test]') is not null 
drop table [test]
create table [test](
[C_ID] int,
[part] int,
[type] int,
[data] varchar(10),
[CSN_UpdateTime] datetime
)
insert [test]
select 1,0,1,'xxxxxxxxxx','2012-5-11 17:59:08' union all
select 1,0,2,'xxxxxxx','2012-5-11 17:59:13' union all
select 1,1,1,'xxxxxxx','2012-5-11 17:59:13' union all
select 1,1,2,'xxxxxx','2012-5-11 17:59:13' union all
select 1,2,1,'xxxxxx','2012-5-11 17:59:13' union all
select 1,2,2,'xxxxxx','2012-5-11 17:59:13' union all
select 1,0,1,'xxxxxxxxxx','2012-5-11 18:00:18' union all
select 1,0,2,'xxxxxxx','2012-5-11 18:00:18' union all
select 1,1,1,'xxxxxxx','2012-5-11 18:00:18' union all
select 1,1,2,'xxxxxx','2012-5-11 18:00:18' union all
select 1,2,1,'xxxxxx','2012-5-11 18:00:18' union all
select 1,2,2,'xxxxxx','2012-5-11 18:00:18'

;with t
as(
select [C_ID],[part],[data],px=ROW_NUMBER()over(partition by part order by getdate())
from test a where a.CSN_UpdateTime=(select MAX(b.CSN_UpdateTime) from test b
where a.C_ID=b.C_ID and a.part=b.part and a.[type]=b.[type])
)
select t.C_ID,t.part,min(t.data) as data1,max(m.data) as data2 from
t inner join
t m on t.C_ID=m.C_ID and t.part=m.part and t.px=m.px
where t.C_ID=1
group by t.C_ID,t.part
/*
C_ID    part    data1    data2
1    0    xxxxxxx    xxxxxxxxxx
1    1    xxxxxx    xxxxxxx
1    2    xxxxxx    xxxxxx
*/

------解决方案--------------------

探讨


SQL code

--> 测试数据：[test]
if object_id('[test]') is not null 
drop table [test]
create table [test](
[C_ID] int,
[part] int,
[type] int,
[data] varchar(10),
[CSN_UpdateTime] datetime
)
insert [test]……