实例1:
sql只查一次未优化
SELECT * FROM ( SELECT COUNT(company_name) AS countnum, company_name, tel_tru, tel_full FROM ( SELECT company_name, tel_full, case when length(tel_l)<=12 then RIGHT(tel_l,7) when length(tel_l)>12 then RIGHT(tel_l,12) end tel_tru FROM ( SELECT company_name, REPLACE(tel,' ','') tel_l, tel tel_full FROM t_contact) a ) b GROUP BY tel_tru ) c WHERE countnum >= 10 |
实例2:
select
case
when locate('省',province_name)>0 then replace(province_name,'省','')
when locate('市',province_name)>0 then replace(province_name,'市','')
when locate('广西壮族自治区',province_name)>0 then replace(province_name,'壮族自治区','')
when locate('新疆维吾尔自治区',province_name)>0 then replace(province_name,'维吾尔自治区','')
when locate('西藏自治区',province_name)>0 then replace(province_name,'自治区','')
when locate('宁夏回族自治区',province_name)>0 then replace(province_name,'回族自治区','')
when locate('内蒙古自治区',province_name)>0 then replace(province_name,'自治区','')
else province_name
end province_name,
sum(curr_position) value from t_cal_positions where left(statistic_time,7) = '2018-12' group by province_name
效果: