示例:按年龄和性别分组统计人员
SELECT
a.sex,
a.nld AS ageGroup,
count( 1 ) AS people
FROM
(
SELECT
td.sex,
CASE
WHEN td.age >= 18
AND td.age <= 30 AND td.sex = '1' THEN '18-30' WHEN td.age >= 18
AND td.age <= 30 AND td.sex = '2' THEN '18-30' WHEN td.age >= 31
AND td.age <= 40 AND td.sex = '1' THEN '31-40' WHEN td.age >= 31
AND td.age <= 40 AND td.sex = '2' THEN '31-40' WHEN td.age >= 41
AND td.age <= 50 AND td.sex = '1' THEN '41-50' WHEN td.age >= 41
AND td.age <= 50 AND td.sex = '2' THEN '41-50' WHEN td.age >= 51
AND td.age <= 55 AND td.sex = '1' THEN '51-55' WHEN td.age >= 51
AND td.age <= 55 AND td.sex = '2' THEN '51-55' WHEN td.age >= 56
AND td.age < 60 AND td.sex = '1' THEN '56-60' WHEN td.age >= 56
AND td.age < 60 AND td.sex = '2' THEN '56-60' WHEN td.age >= 60
AND td.sex = '1' THEN
'60以上'
WHEN td.age >= 60
AND td.sex = '2' THEN
'60以上'
END AS nld
FROM
(
SELECT
m.sex,
m.card_no,
m.birth_date,
FLOOR( PERIOD_DIFF( DATE_FORMAT( NOW(), '%Y%m' ), DATE_FORMAT( CONCAT( m.birth_date, '-01' ), '%Y%m' ))/ 12 ) AS age
FROM
tb_cloud_member m
LEFT JOIN tb_cloud_laborun l ON l.laborun_code = m.laborun_code
LEFT JOIN tb_cloud_enterprises tce ON tce.id = m.unit_id
AND l.is_deleted = 0
AND tce.is_deleted = 0
WHERE
1 = 1
AND m.is_deleted = 0
AND l.is_laborun = 1
AND m.is_new_occupation = 2
AND ( m.retirement_flag IS NULL OR m.retirement_flag = '' OR m.retirement_flag = '0' OR m.retirement_flag = '2' )
AND (
m.job_status IS NULL
OR m.job_status NOT IN ( 4, 5, 6 ))) td
) a
WHERE
a.nld IS NOT NULL
GROUP BY
nld,
a.sex;