根据身份证号计算用户年龄SQL语句:

SELECT DATE_FORMAT(NOW(), '%Y') - SUBSTRING('370687199005050000',7,4) AS age FROM table_name

统计年龄段人数SQL语句:

SELECT
    (
        CASE
        WHEN age < 50 THEN
            '50岁以下'
        WHEN age < 60 THEN
            '50-59岁'
        WHEN age < 70 THEN
            '60-69岁'
        WHEN age < 80 THEN
            '70-79岁'
        ELSE
            '80岁以上'
        END
    ) AS age_range,
    COUNT(age) AS count
FROM
    (
        SELECT
            (
                DATE_FORMAT(NOW(), '%Y') - SUBSTRING(sq_party_card_id, 7, 4)
            ) AS age
        FROM
            table_name
    ) AS a
GROUP BY
    age_range

如未注明,均为原创,转载请注明来自Peak Xin's Blog

No Comments »