Mysql - 把数据根据区间分类 统计该区间的数量 ELt INTERVAL联合使用以及case ..when的运用

ELt INTERVAL联合使用 有一个缺点就是没有数据的那一个区间那个列就不会显示出来，0都不显示，后面就用了另外一种写法。

SELECT
        elt( INTERVAL ( a.age_diff,0,30, 35, 45, 50,60,65 ), '30岁以下', '30-35岁', '35-45岁','45-50岁','50-60岁','60-65岁','65岁以上') AS age_diff,
        count( a.id ) AS num
        FROM
        (
        SELECT
        id,
        ( SELECT CONVERT ( DATE_FORMAT( NOW( ), '%Y' ), SIGNED ) - a.YEAR + 0 ) AS age_diff
        FROM
        ( SELECT id, substring( sfzh, 7, 4 ) AS YEAR, YEAR ( NOW( ) ) FROM bs_dy GROUP BY zgxl ) a
        ) a
        GROUP BY
        elt( INTERVAL ( a.age_diff,0, 30, 35, 45, 50,60,65), '30岁以下', '30-35岁', '35-45岁','45-50岁','50-60岁','60-65岁','65岁以上' )

、SELECT
	aag.NAME as age_diff,
	count( a.namesa )  as num
FROM
	as_age_group aag
	LEFT JOIN (
SELECT
CASE
	
WHEN
	tab.age_diff BETWEEN 20 
	AND 25 THEN
	1 
WHEN tab.age_diff BETWEEN 25 
AND 45 THEN
2 
WHEN tab.age_diff BETWEEN 45 
AND 55 THEN
3 
END AS namesa 
FROM
	(
	SELECT
		id,
		( SELECT CONVERT ( DATE_FORMAT( NOW( ), '%Y' ), SIGNED ) - a.YEAR + 0 ) AS age_diff 
	FROM
		(
		SELECT
			id,
			substring( sfzh, 7, 4 ) AS YEAR,
			YEAR ( NOW( ) ) AS now 
		FROM
			bs_dy 
		GROUP BY
			zgxl 
		) a 
	) tab 
	) a ON a.namesa = aag.type 
GROUP BY
aag.NAME

case … when 和beteen and 结合使用的， 就可以吧米有数据的区间也显示出来。