时间戳

首页 > 解决方案 > Mysql 按年龄分组

mysql - Mysql 按年龄分组

问题描述

我需要帮助，我需要创建这样的报告

age     | total<br>
<= 13   | 10
14      | 0
15      | 5
16      | 2
....    | ....

该报告是按年龄分组的，但是当年龄低于 <= 13 到 13 岁以内时，我无法得到，这是我的查询，它只适用于相同的值，如何将 13 岁以下的值添加到 13 岁以内。

SELECT
              za_hours.za_hour AS age,
              IFNULL(COUNT(stonevoucherchild_claim_user_id), 0) AS total
            FROM
              (SELECT
                13 AS za_hour
              UNION
              SELECT
                14 AS za_hour
              UNION
              SELECT
                15 AS za_hour
              UNION
              SELECT
                16 AS za_hour
              UNION
              SELECT
                17 AS za_hour
              UNION
              SELECT
                18 AS za_hour
              UNION
              SELECT
                19 AS za_hour
              UNION
              SELECT
                20 AS za_hour
              UNION
              SELECT
                21 AS za_hour
              UNION
              SELECT
                22 AS za_hour
              UNION
              SELECT
                23 AS za_hour
              UNION
              SELECT
                24 AS za_hour
              UNION
              SELECT
                25 AS za_hour
              UNION
              SELECT
                26 AS za_hour
              UNION
              SELECT
                27 AS za_hour
              UNION
              SELECT
                28 AS za_hour
              UNION
              SELECT
                29 AS za_hour
              UNION
              SELECT
                30 AS za_hour) za_hours
              LEFT JOIN mu_users
                ON za_hours.za_hour = TIMESTAMPDIFF(YEAR, dob, CURDATE())
            GROUP BY za_hours.za_hour
            ORDER BY za_hours.za_hour;

请帮助我，抱歉英语不好

标签： mysqlsqlgroup-bycount

解决方案

我会使用范围比较；这通过在union all派生表中定义开始和结束值来工作；然后您可以使用between将用户分组。我们还可以为每个范围提供描述，我们将在外部查询中使用：

select n.info, count(u.dob) total
from (
    select '<= 13' info, 0 start, 13 end 
    union all select '= 14', 14, 14
    union all select '= 15', 15, 15
    ...
    union all select '= 30', 30, 30
) n
left join mu_users u
    on timestampdiff(year, u.dob, curdate()) between n.start and n.end
group by n.info, n.start, n.end
order by n.start

推荐阅读