SQL根据身份证,统计用户的省份

最新推荐文章于 2023-08-17 11:17:10 发布

hxhh

最新推荐文章于 2023-08-17 11:17:10 发布

阅读量1.7k

点赞数 1

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/qq403580298/article/details/103584704

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

根据身份证 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