mysql年龄分段统计个数

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

SELECT 
    CASE 
        WHEN age BETWEEN 0 AND 12 THEN '儿童'
        WHEN age BETWEEN 13 AND 18 THEN '青少年'
        WHEN age BETWEEN 19 AND 35 THEN '青年'
        WHEN age BETWEEN 36 AND 60 THEN '中年'
        ELSE '老年'
    END AS age_group,
    COUNT(*) AS count
FROM users
GROUP BY age_group;

SELECT 
    CASE 
        WHEN age BETWEEN 0 AND 12 THEN '儿童'
        WHEN age BETWEEN 13 AND 18 THEN '青少年'
        WHEN age BETWEEN 19 AND 35 THEN '青年'
        WHEN age BETWEEN 36 AND 60 THEN '中年'
        ELSE '老年'
    END AS age_group,
    COUNT(*) AS count
FROM users
WHERE age IS NOT NULL
GROUP BY age_group;

-- 创建索引
CREATE INDEX idx_age ON users(age);

-- 分页查询
SELECT 
    CASE 
        WHEN age BETWEEN 0 AND 12 THEN '儿童'
        WHEN age BETWEEN 13 AND 18 THEN '青少年'
        WHEN age BETWEEN 19 AND 35 THEN '青年'
        WHEN age BETWEEN 36 AND 60 THEN '中年'
        ELSE '老年'
    END AS age_group,
    COUNT(*) AS count
FROM users
WHERE age IS NOT NULL
GROUP BY age_group
LIMIT 10 OFFSET 0;