mysql一些应用（坐标，检索，合并，分组，针对身份证）

获取某表内的坐标信息

CREATE TABLE demo1 select * FROM (
SELECT tzz_csyygl_v_his_citycases.CENTER_X,tzz_csyygl_v_his_citycases.CENTER_Y FROM tzz_csyygl_v_his_citycases ) A

select * FROM demo1

针对某表的数据进行检索分组

SELECT COUNT(*) TABLES, table_schema FROM information_schema.TABLES   
WHERE table_schema = 'data_aggregation' GROUP BY table_schema;

合并需要选择的字段

SELECT AREANAME,COUNT(*)
FROM zfw_wgh_v_ldpeo
GROUP BY AREANAME

笔记：select 列名，count（*）

From 表名

Group by 列名

对省份分组

select case
when province_no='11' then '北京市'
when province_no='12' then '天津市'
when province_no='13' then '河北省'
when province_no='14' then '山西省'
when province_no='15' then '内蒙古自治区'
when province_no='21' then '辽宁省'
when province_no='22' then '吉林省'
when province_no='23' then '黑龙江省'
when province_no='31' then '上海市'
when province_no='32' then '江苏省'
when province_no='33' then '浙江省'
when province_no='34' then '安徽省'
when province_no='35' then '福建省'
when province_no='36' then '江西省'
when province_no='37' then '山东省'
when province_no='41' then '河南省'
when province_no='42' then '湖北省'
when province_no='43' then '湖南省'
when province_no='44' then '广东省'
when province_no='45' then '广西壮族自治区'
when province_no='46' then '海南省'
when province_no='50' then '重庆市'
when province_no='51' then '四川省'
when province_no='52' then '贵州省'
when province_no='53' then '云南省'
when province_no='54' then '*自治区'
when province_no='61' then '陕西省'
when province_no='62' then '甘肃省'
when province_no='63' then '青海省'
when province_no='64' then '宁夏回族自治区'
when province_no='65' then '**自治区'
when province_no='71' then '*省'
when province_no='81' then '香港特别行政区'
when province_no='82' then '澳门特别行政区'
else '未知'     
end as shengfen,total as counts 
from (select left(IDENTIFICATION_NUMBER,2) as province_no,count(*) as total from zfw_wgh_v_ldpeo
group by province_no ) bak

并排序（top10）

select case
when province_no='11' then '北京市'
when province_no='12' then '天津市'
when province_no='13' then '河北省'
when province_no='14' then '山西省'
when province_no='15' then '内蒙古自治区'
when province_no='21' then '辽宁省'
when province_no='22' then '吉林省'
when province_no='23' then '黑龙江省'
when province_no='31' then '上海市'
when province_no='32' then '江苏省'
when province_no='33' then '浙江省'
when province_no='34' then '安徽省'
when province_no='35' then '福建省'
when province_no='36' then '江西省'
when province_no='37' then '山东省'
when province_no='41' then '河南省'
when province_no='42' then '湖北省'
when province_no='43' then '湖南省'
when province_no='44' then '广东省'
when province_no='45' then '广西壮族自治区'
when province_no='46' then '海南省'
when province_no='50' then '重庆市'
when province_no='51' then '四川省'
when province_no='52' then '贵州省'
when province_no='53' then '云南省'
when province_no='54' then '*自治区'
when province_no='61' then '陕西省'
when province_no='62' then '甘肃省'
when province_no='63' then '青海省'
when province_no='64' then '宁夏回族自治区'
when province_no='65' then '**自治区'
when province_no='71' then '*省'
when province_no='81' then '香港特别行政区'
when province_no='82' then '澳门特别行政区'
else '未知'     
end as shengfen,total as counts 
from (select left(IDENTIFICATION_NUMBER,2) as province_no,count(*) as total from zfw_wgh_v_ldpeo
group by province_no ) bak ORDER BY counts DESC LIMIT 10

 年龄占比饼状图（20区间）

CREATE table tmp_0105 SELECT * FROM(
SELECT ID,name,(YEAR(CURDATE()) - SUBSTRING(`IDENTIFICATION_NUMBER`,7,4)) AS ages
FROM zfw_wgh_v_ldpeo HAVING ages <100 ) A

CREATE table Age_ratio SELECT * FROM(
SELECT ages,(CASE 
	WHEN ages < 20 THEN '0-19'
	when ages < 40 THEN '20-39'
	WHEN ages < 60 THEN '40-59'
	when ages < 80 then '60-79'
	ELSE '89+'
	END)B from tmp_0105
ORDER BY ages ) A

CREATE TABLE Age_ratio_20 SELECT * FROM(
select B,COUNT(B) C  FROM Age_ratio
GROUP BY B )D

#SELECT * from Age_ratio_20

#drop table Age_ratio

 

笔记：对事件

select case
when CLASS='002001' then '市容环境-垃圾箱'
when CLASS='002003' then '施工管理'
when CLASS='002019' then '河道'
when CLASS='002017' then '环保'
when CLASS='002004' then '街面秩序'
when CLASS='002002' then '宣传广告'
when CLASS='002006'OR CLASS='002020'OR CLASS='002006' OR CLASS='002005' then '其他事件'
when CLASS='002018' then '十九大'
when CLASS='002003' then '施工管理'
when CLASS='002016' then '环保巡查'
when CLASS='001003' then '市容环境'
when CLASS='001002' then '道路交通'
when CLASS='001001' then '公用设施'
when CLASS='001004' then '园林绿化'
else '未知'
end as CLASS_FULL,total as counts 
from (select left(CLASS_FULL_IDXCODE,6) as CLASS,count(*) as total from TZZ_CSYYGL_V_HIS_CITYCASES
group by CLASS) bak ORDER BY counts DESC

 

本文地址：https://blog.csdn.net/qq_37392932/article/details/85984754