mysql小技巧-不写函数实现split截取_MySQL

mysql小技巧-不写函数实现split截取

1. 不写函数实现split截取

场景：dayNewStat+meilishuo+ipad+2003 （dayNewStat+meilishuo+iphone+2003）(dayNewStat+meilishuo+android+2003) 只取最后一位

设想：split('+')[-1] (python写法)

实现：有严格的格式要求，取巧方法，无技术含量

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

场景：只要status是ok的，且id最小

id      type     status001    aaa      ok002    aaa      error010    aaa      ok003    bbb     ok

实现：去重 & 按条件筛选

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