根据身份证 fcredit_id, 截取前两位,统计用户的省份
select
sum(case when SUBSTRING(fcredit_id,1,2)='11' then 1 else 0 end) as beijing,
sum(case when SUBSTRING(fcredit_id,1,2)='12' then 1 else 0 end) as tainjing,
sum(case when SUBSTRING(fcredit_id,1,2)='13' then 1 else 0 end) as heibei,
sum(case when SUBSTRING(fcredit_id,1,2)='14' then 1 else 0 end) as shanxi,
sum(case when SUBSTRING(fcredit_id,1,2)='15' then 1 else 0 end) as neimenggu,
sum(case when SUBSTRING(fcredit_id,1,2)='21' then 1 else 0 end) as liaoning,
sum(case when SUBSTRING(fcredit_id,1,2)='22' then 1 else 0 end) as jiling,
sum(case when SUBSTRING(fcredit_id,1,2)='23' then 1 else 0 end) as heilongjiang,
sum(case when SUBSTRING(fcredit_id,1,2)='31' then 1 else 0 end) as shanghai,
sum(case when SUBSTRING(fcredit_id,1,2)='32' then 1 else 0 end) as jiangsu,
sum(case when SUBSTRING(fcredit_id,1,2)='33' then 1 else 0 end) as zhejiang,
sum(case when SUBSTRING(fcredit_id,1,2)='34' then 1 else 0 end) as anhui,
sum(case when SUBSTRING(fcredit_id,1,2)='35' then 1 else 0 end) as fujian,
sum(case when SUBSTRING(fcredit_id,1,2)='36' then 1 else 0 end) as jiangxi,
sum(case when SUBSTRING(fcredit_id,1,2)='37' then 1 else 0 end) as shangdong,
sum(case when SUBSTRING(fcredit_id,1,2)='41' then 1 else 0 end) as henan,
sum(case when SUBSTRING(fcredit_id,1,2)='42' then 1 else 0 end) as hubei,
sum(case when SUBSTRING(fcredit_id,1,2)='43' then 1 else 0 end) as hunan,
sum(case when SUBSTRING(fcredit_id,1,2)='44' then 1 else 0 end) as guangdong,
sum(case when SUBSTRING(fcredit_id,1,2)='45' then 1 else 0 end) as guangxi,
sum(case when SUBSTRING(fcredit_id,1,2)='46' then 1 else 0 end) as hainan,
sum(case when SUBSTRING(fcredit_id,1,2)='50' then 1 else 0 end) as chongqin,
sum(case when SUBSTRING(fcredit_id,1,2)='51' then 1 else 0 end) as sichuan,
sum(case when SUBSTRING(fcredit_id,1,2)='52' then 1 else 0 end) as guizhou,
sum(case when SUBSTRING(fcredit_id,1,2)='53' then 1 else 0 end) as yunnan,
sum(case when SUBSTRING(fcredit_id,1,2)='54' then 1 else 0 end) as xizang,
sum(case when SUBSTRING(fcredit_id,1,2)='61' then 1 else 0 end) as shanxi,
sum(case when SUBSTRING(fcredit_id,1,2)='62' then 1 else 0 end) as gansu,
sum(case when SUBSTRING(fcredit_id,1,2)='63' then 1 else 0 end) as qinghai,
sum(case when SUBSTRING(fcredit_id,1,2)='64' then 1 else 0 end) as ningxia,
sum(case when SUBSTRING(fcredit_id,1,2)='65' then 1 else 0 end) as xinjiang,
sum(case when SUBSTRING(fcredit_id,1,2)='71' then 1 else 0 end) as taiwan,
sum(case when SUBSTRING(fcredit_id,1,2)='81' then 1 else 0 end) as xianggang,
sum(case when SUBSTRING(fcredit_id,1,2)='91' then 1 else 0 end) as aomen
from jz_tmp.borrower_fcredit_20190426