mysql 常用查询模型

1.一个字段有多个可取值，先要统计每个可取值数量

demo1 统计从 常驻/流动人口

-- 统计常驻人口、流动人口
-- migration_type 人口流动 (1:常住;2:常访客;3:临时访客)
select sum(mobile) as mobile,sum(permanent) as permanent
from
(select 
case when migration_type='1'  then 1 else 0 end mobile ,
case when migration_type in('2','3')  then 1 else 0 end permanent
from sys_staff_info where domain = 'default') tmp

demo2

-- 分别统计统计独居老人、养犬人员、五保户、重点关注人群、其他
-- people_type 人群类型（0:正常;1:独居老人;2:养犬人员;3:五保户;4:疆藏;5:精神病;6:刑满释放;7:社区矫正;8:吸毒人员;9:上访人员）
select sum(olderly_alone) as olderly_alone,sum(dog_keeeper) as dog_keeeper,
sum(low_salary) as low_salary,sum(focus_crowd) as focus_crowd,sum(others) as others
from
(select 
case when people_type='1'  then 1 else 0 end olderly_alone,
case when people_type='2'  then 1 else 0 end dog_keeeper,
case when people_type='3'  then 1 else 0 end low_salary,
case when people_type in('4','5','6','7','8','9')  then 1 else 0 end focus_crowd,
case when people_type in('0','')  then 1 else 0 end others
from sys_staff_info where domain = 'default') tmp

demo3
 

-- 统计疫情级别
-- country 国籍(0:中国;1:外国)  sys_city 0:低;1:中;2:高
select sum(low) as low, sum(middle) as middle, sum(high) as high, sum(alien) as alien from (
 select case when (tb.level is null or  tb.level = '0') and ta.country = '0' then 1 else 0 end low,
 case when tb.level = '1' and ta.country = '0' then 1 else 0 end middle,
 case when tb.level = '2' and ta.country = '0' then 1 else 0 end high,
 case when ta.country = '1' then 1 else 0 end alien
 from sys_staff_info ta left join sys_city tb on ta.city = tb.name
 where domain = 'default'
) ta

 

2. 一些设计表时注意

-- uft下中文 能存33个中文字符。如果有空值，最好 not null default ''
desc_info                     varchar(100) not null default '' comment '数据集备注信息'

--  日期自动更新
updated_time				  datetime not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  comment '更新日期'

-- 多种类型，可以用char(1) 。mysql不支持bool，用char(1) or tinyint 
	dataset_source_type          char(1) not null default 1 comment '数据类型：1上传图片；2上传压缩包'