mysql分类统计

最新推荐文章于 2024-08-21 09:02:28 发布

jshyzx1999

最新推荐文章于 2024-08-21 09:02:28 发布

阅读量591

点赞数

本文链接：https://blog.csdn.net/jshyzx1999/article/details/106546445

版权

//退休人员统计

SELECT
coalesce(public_institution.pub_class,'类别汇总') as '编制单位类别',
coalesce(public_institution.pub_id,'单位汇总') as '编制单位ID',
coalesce(public_institution.pub_longname,concat(public_institution.pub_longname,'单位汇总')) as '编制单位名称',

count(case when teachers.is_leave='78' AND teachers.leave_type='67' then 1 end) as '自然减员离职',
count(case when teachers.is_leave='78' AND teachers.leave_type='67' and teachers.leave_method='69' then 1 end) as '自然减员-死亡人数',
count(case when teachers.is_leave='78' AND teachers.leave_type='67' and teachers.leave_method='68' then 1 end) as '自然减员-退休人数'
FROM
public_institution
LEFT JOIN teachers ON public_institution.pub_id = teachers.pub_id
GROUP BY public_institution.pub_class,public_institution.pub_id
WITH ROLLUP

//性别统计

COUNT(case when teachers.sex='35' then 1 end) as '男',
COUNT(case when teachers.sex='36' then 1 end) as '女'
FROM
public_institution
LEFT JOIN teachers ON public_institution.pub_id = teachers.pub_id AND is_leave='79'
GROUP BY public_institution.pub_class,public_institution.pub_id
WITH ROLLUP

//性别统计：用子查询/先排序

SELECT ifnull(pub_class,'total') as pub_class,longname,p.sex,IFNULL(sum(sex='35'),0) AS `35`,
IFNULL(sum(sex='36'),0) AS `36`
FROM
(SELECT public_institution.pub_class,public_institution.pub_id,public_institution.pub_longname as longname,teachers.sex
FROM public_institution
LEFT JOIN teachers
ON public_institution.pub_id = teachers.pub_id AND isnull(teachers.is_leave)
WHERE isnull(is_leave)
ORDER BY pub_id ) as p
GROUP BY p.pub_class
WITH ROLLUP

//计算机年龄，对年对月对日

SELECT
public_institution.pub_longname,
teachers.tname,
teachers.is_leave,
teachers.sid,
(substring(now(),1,4)-substring(teachers.sid,7,4))-(substring(teachers.sid,11,4)-date_format(now(),'%m%d')>0) AS age,
substring(now(),1,4) as nowyear,
substring(teachers.sid,7,4) as yearSID,
substring(teachers.sid,11,4) as temp,
date_format(now(),'%m%d') as temp1,
(substring(teachers.sid,11,4)-date_format(now(),'%m%d')>0) as temp_temp1
FROM
public_institution
LEFT JOIN teachers ON public_institution.pub_id = teachers.pub_id

//性别、年龄汇总

COUNT(case when teachers.sex='35' and (substring(now(),1,4)-substring(teachers.sid,7,4))-(substring(teachers.sid,11,4)-date_format(now(),'%m%d')>0)<30 then 1 end) as '男',
COUNT(case when teachers.sex='36' then 1 end) as '女'
FROM
public_institution
LEFT JOIN teachers ON public_institution.pub_id = teachers.pub_id AND is_leave='79'
GROUP BY public_institution.pub_class,public_institution.pub_id
WITH ROLLUP

//mysql通过身份证号码分组统计性别、年龄段，所属省份--2018-02-05

//https://www.jianshu.com/p/3f2b1726c4d3

1.年龄：
(YEAR(CURDATE()) - SUBSTRING(card_id,7,4)) AS 年龄,
2.所属省份：
CASE LEFT(card_id,2) WHEN 11 THEN '北京' WHEN 12 THEN '天津' WHEN 13 THEN '河北' WHEN 14 THEN '山西' WHEN 15 THEN '内蒙古' WHEN 21 THEN '辽宁' WHEN 22 THEN '吉林' WHEN 23 THEN '黑龙江' WHEN 31 THEN '上海' WHEN 32 THEN '江苏' WHEN 33 THEN '浙江' WHEN 34 THEN '安徽' WHEN 35 THEN '福建' WHEN 36 THEN '江西' WHEN 37 THEN '山东' WHEN 41 THEN '河南' WHEN 42 THEN '湖北' WHEN 43 THEN '湖南' WHEN 44 THEN '广东' WHEN 45 THEN '广西' WHEN 46 THEN '海南' WHEN 50 THEN '重庆' WHEN 51 THEN '四川' WHEN 52 THEN '贵州' WHEN 53 THEN '云南' WHEN 54 THEN '西藏' WHEN 61 THEN '陕西' WHEN 62 THEN '甘肃' WHEN 63 THEN '青海' WHEN 64 THEN '宁夏' WHEN 65 THEN '新疆' END AS 所属省份,
3.性别：
IF (MOD(SUBSTRING(card_id,17,1),2),'男','女') AS 性别

以上内容来是csdn ，本来自己已经实现了，然后看到这样的写法的确是很简单的就在此记录一下,以下是我做的统计sql语句
select COUNT(a.id),
SUM(CASE WHEN a.sex='男' THEN 1 ELSE 0 END) AS '男总数',
SUM(CASE WHEN a.sex='女' THEN 1 ELSE 0 END) AS '女总数',
SUM(CASE WHEN a.age <18 THEN 1 ELSE 0 END) AS '18岁以下',
SUM(CASE WHEN a.age >=18 AND a.age <=30 THEN 1 ELSE 0 END) AS '18--30岁',
SUM(CASE WHEN a.age >30 AND a.age <=40 THEN 1 ELSE 0 END) AS '30--40岁',
SUM(CASE WHEN a.age >40 AND a.age <=50 THEN 1 ELSE 0 END) AS '40--50岁',
SUM(CASE WHEN a.age >50 AND a.age <=60 THEN 1 ELSE 0 END) AS '50--60岁',
SUM(CASE WHEN a.age >60 THEN 1 ELSE 0 END) AS '60岁以上'
from(SELECT m.id,m.user_name,m.user_phone,Substr(mi.idcard,7,8) as year,YEAR(NOW())-Substr(mi.idcard,7,4) age,mi.idcard,if (mod(substr(mi.idcard,17,1),2),'男','女') sex, sum(bi.investor_capital) capital FROM lzh_members m LEFT JOIN lzh_member_info mi on mi.uid=m.id LEFT JOIN lzh_borrow_investor bi ON m.id=bi.investor_uid where bi.status > 3 and mi.idcard >100 GROUP BY m.id) a

简单注释一下：
SELECT m.id,m.user_name,m.user_phone,Substr(mi.idcard,7,8) as year,YEAR(NOW())-Substr(mi.idcard,7,4) age,mi.idcard,if (mod(substr(mi.idcard,17,1),2),'男','女') sex, sum(bi.investor_capital) capital FROM lzh_members m LEFT JOIN lzh_member_info mi on mi.uid=m.id LEFT JOIN lzh_borrow_investor bi ON m.id=bi.investor_uid where bi.status > 3 and mi.idcard >100 GROUP BY m.id
括号中是查询出的总记录数据
if (mod(substr(mi.idcard,17,1),2),'男','女') sex 这里是做的判断（括号中是截取的身份证第17位） mod 除2求余
YEAR(NOW())-Substr(mi.idcard,7,4) age 这个是当前年年份-身份证的年份=实际年龄

我这里没有区分地域分布只做了性别和年龄段

作者：二划儿
链接：https://www.jianshu.com/p/3f2b1726c4d3
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

select public_institution.pub_id,
public_institution.pub_longname,

teachers.is_leave,
teachers.sid,
coalesce(teachers.sex,'合计数'),

SUM(CASE WHEN (substring(now(),1,4)-substring(teachers.sid,7,4))-(substring(teachers.sid,11,4)-date_format(now(),'%m%d')>0) >=18 AND (substring(now(),1,4)-substring(teachers.sid,7,4))-(substring(teachers.sid,11,4)-date_format(now(),'%m%d')>0) <=30 THEN 1 ELSE 0 END)
AS '18--30周岁(含）',
SUM(CASE WHEN (substring(now(),1,4)-substring(teachers.sid,7,4))-(substring(teachers.sid,11,4)-date_format(now(),'%m%d')>0) >30 AND (substring(now(),1,4)-substring(teachers.sid,7,4))-(substring(teachers.sid,11,4)-date_format(now(),'%m%d')>0) <=70 THEN 1 ELSE 0 END)
AS '31--60周岁'

from public_institution
LEFT JOIN teachers on public_institution.pub_id=teachers.pub_id AND isnull(is_leave)
GROUP BY public_institution.pub_id,teachers.sex WITH ROLLUP