mysql小技巧-不寫函數實現split截取 1. 不寫函數實現split截取 場景:dayNewStat+meilishuo+ipad+2003 (dayNewStat+meilishuo+iphone+2003)(dayNewStat+meilishuo+android+2003) 只取最後一位 設想:split('+')[-1] (python寫法) 實現:有嚴格的格式要求,取巧方法,無技術含量
1 substring(record_tag,locate('+',replace(record_tag,'dayNewStat+meilishuo+',' dayNewStat-meilishuo-'))) 2. having + min + if
場景:只要status是ok的,且id最小
id type status 001 aaa ok 002 aaa error 010 aaa ok 003 bbb ok
實現:去重 & 按條件篩選
1 select stat_date,record_tag,substring(record_tag,locate('+',replace(record_tag,'dayNewStat+meilishuo+',' dayNewStat-meilishuo-'))) as client_id,attr_value 2 from t_stat_daynew_classify_201306 3 where 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 = 2039497004 4 group by stat_date,record_tag 5 having 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