SQL根据身份证统计年龄段人数

SELECT 
SUM(CASE WHEN age <=20 Then 1 ELSE 0 END) AS '0~20'
, SUM( CASE WHEN age BETWEEN 21 AND 40 THEN 1 ELSE 0 END ) AS '21~40'
, SUM(CASE WHEN age BETWEEN 41 AND 60 THEN 1 ELSE 0 END) AS '41~60'
, SUM(CASE WHEN age BETWEEN 61 AND 80 THEN 1 ELSE 0 END) AS '61~80' 
FROM
(
SELECT DATE_FORMAT(check_time,'%Y') AS date
, FLOOR(DATEDIFF(DATE_FORMAT(now(), '%Y%m%d'), SUBSTRING(id_card,7,8))/365.25) AS age  FROM user_info) AS t 
WHERE date = '2021'