mysql 用一条sql按多个条件分组统计

原创已于 2024-07-19 16:46:24 修改 · 831 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #sql

于 2024-04-27 10:42:27 首次发布

本文是一篇SQL查询示例，用于统计不同年龄段和性别的人员数量，数据来源于某会员表和劳动关系表，主要关注在职员工的年龄分布情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

示例：按年龄和性别分组统计人员

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;