mysql单表统计不同类型计数实例

mysql单表统计不同类型计数实例

SELECT
count(*) as total,
ifnull(sum(case when sex = ‘男’ then 1 else 0 end),0) as man,
ifnull(sum(case when sex = ‘女’ then 1 else 0 end),0) as woman,
ifnull(sum(case when political_status = ‘党员’ then 1 else 0 end),0) as communist,
ifnull(sum(case when political_status = ‘团员’ then 1 else 0 end),0) as communistYouth,
ifnull(sum(case when political_status = ‘群众’ then 1 else 0 end),0) as masses,
ifnull(sum(case when schooling = ‘小学’ then 1 else 0 end),0) as primarySchool,
ifnull(sum(case when schooling = ‘初中’ then 1 else 0 end),0) as middleSchool,
ifnull(sum(case when schooling = ‘高中’ then 1 else 0 end),0) as highSchool,
ifnull(sum(case when schooling = ‘本科’ then 1 else 0 end),0) as undergraduate,
ifnull(sum(case when schooling = ‘研究生’ then 1 else 0 end),0) as postgraduate,
ifnull(sum(case when identity = ‘0’ then 1 else 0 end),0) as selfWorkers,
ifnull(sum(case when identity = ‘1’ then 1 else 0 end),0) as outWorker,
ifnull(sum(case when identity = ‘2’ then 1 else 0 end),0) as labor,
ifnull(sum(case when identity = ‘3’ then 1 else 0 end),0) as hourlyWorker,
ifnull(sum(case when level is null then 1 else 0 end),0) as notRated,
ifnull(sum(case when level = ‘初级’ then 1 else 0 end),0) as elementary,
ifnull(sum(case when level = ‘中级’ then 1 else 0 end),0) as intermediate,
ifnull(sum(case when level = ‘高级’ then 1 else 0 end),0) as senior,
ifnull(sum(case when level = ‘技师’ then 1 else 0 end),0) as technician,
ifnull(sum(case WHEN (TIMESTAMPDIFF(YEAR,cnpc_employee.birth,NOW()) BETWEEN 21 AND 30) THEN 1 else 0 end),0) as youngPeople,
ifnull(sum(case WHEN (TIMESTAMPDIFF(YEAR,cnpc_employee.birth,NOW()) BETWEEN 31 AND 40) THEN 1 else 0 end),0) as earlyMiddleAged,
ifnull(sum(case WHEN (TIMESTAMPDIFF(YEAR,cnpc_employee.birth,NOW()) BETWEEN 41 AND 50) THEN 1 else 0 end),0) as middleAged,
ifnull(sum(case WHEN (TIMESTAMPDIFF(YEAR,cnpc_employee.birth,NOW()) BETWEEN 51 AND 60) THEN 1 else 0 end),0) as oldPeople
FROM
cnpc_employee
WHERE
STATUS = 0
AND on_job = 0
AND hose != ‘12356’
AND (type = 1 OR type = 2)
–
– and city = #{city}
–