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

bitsCN.com

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(&#39;+&#39;,replace(record_tag,&#39;dayNewStat+meilishuo+&#39;,&#39; dayNewStat-meilishuo-&#39;))) as client_id,attr_value2from t_stat_daynew_classify_2013063where  record_type=&#39;dayNewStat+app+device+clientID&#39; and record_tag like &#39;dayNewStat+meilishuo+%&#39;  and attr_name =&#39;totalDID&#39; 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 &#39;dayNewStat+meilishuo+android%&#39;) or (client_id>2000 and client_id<5000 and record_tag like &#39;dayNewStat+meilishuo+ipad%&#39;)  or (client_id>5000 and client_id<10000),&#39;ok&#39;,&#39;del&#39; ) = &#39;ok&#39; order by stat_date,client_id

 

bitsCN.com