数据检索拆分问题

[veritasfx]

表格如下：
SERIAL_NUM ATTR_DATA TRANSACTION_ID TRANSACTION_DATE
-------------------------------------------------------------------------------------------
1XP02503 TO_SUB_INV_CODE=ECP;TO_AREA_CODE=CP; 2820 2012-11-10
1XP02503 TO_SUB_INV_CODE=SIE;TO_AREA_CODE=TLS; 1996 2012-11-10
1XP02503 FAMILY=9TE;PART_NUM=9TE066-999; 1983 2012-10-11
1XP02506 TO_SUB_INV_CODE=ECP;TO_AREA_CODE=CP; 3344 2012-10-11
1XP02506 FAMILY=9TE;PART_NUM=9TE066-999; 1345 2012-10-11

拆分要求：
1，剔除不含TO_SUB_INV_CODE 和 TO_AREA_CODE 关键字的记录
2，挑选最近的那个记录
上面的表格数据检索拆分结果如下：
SERIAL_NUM ATTR_DATA TRANSACTION_ID TRANSACTION_DATE
-------------------------------------------------------------------------------------------
1XP02503 TO_SUB_INV_CODE=ECP;TO_AREA_CODE=CP; 2820 2012-11-10
1XP02506 TO_SUB_INV_CODE=ECP;TO_AREA_CODE=CP; 3344 2012-10-11

哪位大侠能够帮忙？我用的是SQL SERVER 2008 EXPRESS

[bean_sql]

--时间有两条相同的，而且都是最新的，所以多出来一条数据
with tb as
(
	select '1XP02503' as SERIAL_NUM,'TO_SUB_INV_CODE=ECP;TO_AREA_CODE=CP;' as ATTR_DATA,2820 as TRANSACTION_ID,'2012-11-10' as TRANSACTION_DATE union all
	select '1XP02503','TO_SUB_INV_CODE=SIE;TO_AREA_CODE=TLS;',1996,'2012-11-10' union all
	select '1XP02503','FAMILY=9TE;PART_NUM=9TE066-999;',1983,'2012-10-11' union all
	select '1XP02506','TO_SUB_INV_CODE=ECP;TO_AREA_CODE=CP;',3344,'2012-10-11' union all
	select '1XP02506','FAMILY=9TE;PART_NUM=9TE066-999;',1345,'2012-10-11'
)
select * from tb
where CHARINDEX('TO_SUB_INV_CODE',ATTR_DATA)>0
	and 
	CHARINDEX('TO_AREA_CODE',ATTR_DATA)>0
	and TRANSACTION_DATE=
	(
		select MAX(TRANSACTION_DATE) from tb t where t.SERIAL_NUM=tb.SERIAL_NUM
	)
/*
SERIAL_NUM ATTR_DATA                             TRANSACTION_ID TRANSACTION_DATE
---------- ------------------------------------- -------------- ----------------
1XP02503   TO_SUB_INV_CODE=ECP;TO_AREA_CODE=CP;  2820           2012-11-10
1XP02503   TO_SUB_INV_CODE=SIE;TO_AREA_CODE=TLS; 1996           2012-11-10
1XP02506   TO_SUB_INV_CODE=ECP;TO_AREA_CODE=CP;  3344           2012-10-11

(3 row(s) affected)
*/

[bean_sql]

--时间存在相同的，取金额最大的一个
with tb as
(
	select '1XP02503' as SERIAL_NUM,'TO_SUB_INV_CODE=ECP;TO_AREA_CODE=CP;' as ATTR_DATA,2820 as TRANSACTION_ID,'2012-11-10' as TRANSACTION_DATE union all
	select '1XP02503','TO_SUB_INV_CODE=SIE;TO_AREA_CODE=TLS;',1996,'2012-11-10' union all
	select '1XP02503','FAMILY=9TE;PART_NUM=9TE066-999;',1983,'2012-10-11' union all
	select '1XP02506','TO_SUB_INV_CODE=ECP;TO_AREA_CODE=CP;',3344,'2012-10-11' union all
	select '1XP02506','FAMILY=9TE;PART_NUM=9TE066-999;',1345,'2012-10-11'
),tb2 as 
(
	select 
		*,
		ROW_NUMBER() over(partition by Serial_num order by TRANSACTION_DATE desc,TRANSACTION_ID desc) as v_rank
	from tb
	where CHARINDEX('TO_SUB_INV_CODE',ATTR_DATA)>0
		and 
		CHARINDEX('TO_AREA_CODE',ATTR_DATA)>0
		and TRANSACTION_DATE=
		(
			select MAX(TRANSACTION_DATE) from tb t where t.SERIAL_NUM=tb.SERIAL_NUM
		)
)
select * from tb2
where v_rank='1'
/*
SERIAL_NUM ATTR_DATA                             TRANSACTION_ID TRANSACTION_DATE v_rank
---------- ------------------------------------- -------------- ---------------- --------------------
1XP02503   TO_SUB_INV_CODE=ECP;TO_AREA_CODE=CP;  2820           2012-11-10       1
1XP02506   TO_SUB_INV_CODE=ECP;TO_AREA_CODE=CP;  3344           2012-10-11       1

(2 row(s) affected)
*/

[bean_sql]

--时间存在相同的，取金额最大的一个
with tb as
(
	select '1XP02503' as SERIAL_NUM,'TO_SUB_INV_CODE=ECP;TO_AREA_CODE=CP;' as ATTR_DATA,2820 as TRANSACTION_ID,'2012-11-10' as TRANSACTION_DATE union all
	select '1XP02503','TO_SUB_INV_CODE=SIE;TO_AREA_CODE=TLS;',1996,'2012-11-10' union all
	select '1XP02503','FAMILY=9TE;PART_NUM=9TE066-999;',1983,'2012-10-11' union all
	select '1XP02506','TO_SUB_INV_CODE=ECP;TO_AREA_CODE=CP;',3344,'2012-10-11' union all
	select '1XP02506','FAMILY=9TE;PART_NUM=9TE066-999;',1345,'2012-10-11'
),tb2 as 
(
	select 
		*,
		ROW_NUMBER() over(partition by Serial_num order by TRANSACTION_DATE desc,TRANSACTION_ID desc) as v_rank
	from tb
	where CHARINDEX('TO_SUB_INV_CODE',ATTR_DATA)>0
		and 
		CHARINDEX('TO_AREA_CODE',ATTR_DATA)>0
		and TRANSACTION_DATE=
		(
			select MAX(TRANSACTION_DATE) from tb t where t.SERIAL_NUM=tb.SERIAL_NUM
		)
)
select * from tb2
where v_rank='1'
/*
SERIAL_NUM ATTR_DATA                             TRANSACTION_ID TRANSACTION_DATE v_rank
---------- ------------------------------------- -------------- ---------------- --------------------
1XP02503   TO_SUB_INV_CODE=ECP;TO_AREA_CODE=CP;  2820           2012-11-10       1
1XP02506   TO_SUB_INV_CODE=ECP;TO_AREA_CODE=CP;  3344           2012-10-11       1

(2 row(s) affected)
*/

[veritasfx]

厉害啊，验证之后完全正确。谢谢了。