问一个比较难的sql语句
表

SQL code
create table VehicleData_tbl (
  CSN_ID bigint identity,
  C_ID int null,
  CSN_Part int null,
  CSN_Type int null,
  CSN_Data varchar(Max) null,
  CSN_UpdateTime datetime null,
  CSN_CreateTime datetime null,
  constraint PK_CARVEHICLEDATA_TBL primary key (CSN_ID)
)


CSN_Part 的值分布在0,1,2,3中
每个 CSN_Part 都有2种类型,用CSN_Type字段来区别(1,2)
VehicleData_tbl 表中每条记录的.CSN_UpdateTime 的值都不一样
例如
C_ID part type data CSN_UpdateTime  
1 0 1 xxxxxxxxxx 2012-5-11 17:59:08
1 0 2 xxxxxxx 2012-5-11 17:59:13
1 1 1 xxxxxxx 2012-5-11 17:59:13  
1 1 2 xxxxxx 2012-5-11 17:59:13
1 2 1 xxxxxx 2012-5-11 17:59:13
1 2 2 xxxxxx 2012-5-11 17:59:13
1 0 1 xxxxxxxxxx 2012-5-11 18:00:18
1 0 2 xxxxxxx 2012-5-11 18:00:18
1 1 1 xxxxxxx 2012-5-11 18:00:18
1 1 2 xxxxxx 2012-5-11 18:00:18
1 2 1 xxxxxx 2012-5-11 18:00:18
1 2 2 xxxxxx 2012-5-11 18:00:18


..............
n 0 1 xxxxxxxxxx
n 0 2 xxxxxxx
n 1 1 xxxxxxx
n 1 2 xxxxxx
n 2 1 xxxxxx
n 2 2 xxxxxx




我现在要取出指定c_Id=x 的 所有part和type 中 CSN_UpdateTime 时间最大的值,输出为一下形式
C_ID part type1_data type2_data  
1 0 xxxxxxxx xxxxxxxxxxxxxx
1 1 xxxxxxxx xxxxxxxxxxxxxx
1 2 xxxxxxxx xxxxxxxxxxxxxx
1 3 xxxxxxxx xxxxxxxxxxxxxx


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

select C_ID,CSN_Part,Max(Case When CSN_Type=1 Then Csn_UpdateTime else '1900-01-01' End) type1_data,
Max(Case When CSN_Type=2 Then Csn_UpdateTime else '1900-01-01' End) type2_data
From VehicleData_tbl
Where C_ID=x
Group by C_ID,CSN_Part
------解决方案--------------------

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]
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'


select [C_ID],[part],
max(case when [type]=1 then [data] end) as [data1],
max(case when [type]=2 then [data] end) as [data2]
from(select * 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))t
group by [C_ID],[part]
/*
C_ID    part    data1    data2
1    0    xxxxxxxxxx    xxxxxxx
1    1    xxxxxxx    xxxxxx
1    2    xxxxxx    xxxxxx
*/