Mysql其他

select *   from 
    (select city,usage_percent from cdn_usage_city_5min where time='{getTime()}'  and city  not like '%未知%' ) as a 
    LEFT  JOIN 
    (select city,carton_rate_percent from carton_city_5min where time ={getTime()}) as b on a.city=b.city
    LEFT JOIN (select cdn_city,rate_percent from schedule_rate_5min where bras_city=cdn_city and time ={getTime()} ) as d on d.cdn_city=b.city
    LEFT JOIN (select city,avg_hot_covered_rate_percent from db_ott_{yestaday_date}.hot_covered_cdn_city where city !="未知CDN") as e on e.city=a.city
    LEFT JOIN (select city,total_count from user_count_city_5min where    time ={getTime()} ) as f on f.city=a.city
    　

连表范例

 

更新或者插入

#如果主键或唯一键 的数据 已经存在 那么就更新   不存在冲突的唯一键就插入 

replace into carton_cdn_5min(city,node,cdnip,stb_bad_quality_percent,affected_bras_area_percent, time) values("温州3","东头2","192.168.0.12",602,702,12);

除数为0

case  when 分母=0 then NULL else 分子/分母 end

select *,new_rate*100 as rate from {self.table} where  (case when ts_count=0 then 0 else m3u8_count/ts_count end) >= {multi}