SQL根据身份证统计年龄段人数
2021-11-10 本文已影响0人
猫的树
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'
