数据检索拆分有关问题

数据检索拆分问题
表格如下：
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

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

SQL code


--时间存在相同的，取金额最大的一个
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)
*/

数据检索拆分有关问题

相关推荐