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 这个是当前年年份-身份证的年份=实际年龄
我这里没有区分地域分布 只做了 性别和年龄段
`
网友评论