问一个比较难的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
[解决办法]
--> 测试数据：[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 allselect 1,0,2,'xxxxxxx','2012-5-11 17:59:13' union allselect 1,1,1,'xxxxxxx','2012-5-11 17:59:13' union allselect 1,1,2,'xxxxxx','2012-5-11 17:59:13' union allselect 1,2,1,'xxxxxx','2012-5-11 17:59:13' union allselect 1,2,2,'xxxxxx','2012-5-11 17:59:13' union allselect 1,0,1,'xxxxxxxxxx','2012-5-11 18:00:18' union allselect 1,0,2,'xxxxxxx','2012-5-11 18:00:18' union allselect 1,1,1,'xxxxxxx','2012-5-11 18:00:18' union allselect 1,1,2,'xxxxxx','2012-5-11 18:00:18' union allselect 1,2,1,'xxxxxx','2012-5-11 18:00:18' union allselect 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 awhere a.CSN_UpdateTime=(select MAX(b.CSN_UpdateTime) from test bwhere a.C_ID=b.C_ID and a.part=b.part))tgroup by [C_ID],[part]/*C_ID part data1 data21 0 xxxxxxxxxx xxxxxxx1 1 xxxxxxx xxxxxx1 2 xxxxxx xxxxxx*/