数据检索拆分问题
表格如下：
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
[解决办法]
--时间存在相同的，取金额最大的一个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 tb2where 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 11XP02506 TO_SUB_INV_CODE=ECP;TO_AREA_CODE=CP; 3344 2012-10-11 1(2 row(s) affected)*/