Tips for mysql-implement split truncation without writing a function

Tips for mysql-implement split truncation without writing a function 1. if you do not write a function to implement split truncation, dayNewStat + meilishuo + ipad + 2003 (dayNewStat + meilishuo + iphone + 2003) (dayNewStat + meilishuo + android + 2003) takes the last assumption: split ('+') [-1] (written in python) Implementation: strict format requirements, clever methods, no technical content

1substring(record_tag,locate('+',replace(record_tag,'dayNewStat+meilishuo+',' dayNewStat-meilishuo-')))2. having + min + if

 

Scenario: as long as the status is OK and the id is the smallest

id      type     status001    aaa      ok002    aaa      error010    aaa      ok003    bbb     ok

 

Implementation: deduplication & conditional Filtering

1select stat_date,record_tag,substring(record_tag,locate('+',replace(record_tag,'dayNewStat+meilishuo+',' dayNewStat-meilishuo-'))) as client_id,attr_value2from t_stat_daynew_classify_2013063where  record_type='dayNewStat+app+device+clientID' and record_tag like 'dayNewStat+meilishuo+%'  and attr_name ='totalDID' and attr_name_hash = 1380872519 and record_type_hash = 20394970044group by stat_date,record_tag5having min(id) and if( (client_id>10000 and record_tag like 'dayNewStat+meilishuo+android%') or (client_id>2000 and client_id<5000 and record_tag like 'dayNewStat+meilishuo+ipad%')  or (client_id>5000 and client_id<10000),'ok','del' ) = 'ok' order by stat_date,client_id