mysql - 对 mysql count 函数非常失望

Question

为了这个我绞尽脑汁两天了。我需要计算每个年龄段的成员，但不知道如何计算。我已经做到了这一点，但这只显示了成员的年龄段，但没有计算在内。

SELECT
CASE
    WHEN YEAR(CURRENT_DATE()) - YEAR(birth_date) - (RIGHT(CURRENT_DATE(), 5) < RIGHT (birth_date, 5)) BETWEEN 12 AND 18 THEN '12-18'
    WHEN YEAR(CURRENT_DATE()) - YEAR(birth_date) - (RIGHT(CURRENT_DATE(), 5) < RIGHT (birth_date, 5)) BETWEEN 19 AND 55 then '19-55'
ELSE '56 or older'
END AS age_group
FROM members
ORDER BY age_group

Answer 1

您已经提取了所需的数据。要对其进行计数，将其嵌入为子查询并通过 group by 子句对每个组的结果进行计数。通过在子查询中为每个成员添加唯一标识符，它允许每个组中的成员计数在组中存活(即，不会将每个组折叠成一行)。

SELECT age_group, COUNT(*) AS group_count
FROM
    (SELECT
        - -  add a unique identifier for each member here as a select attribute, 
        CASE 
            WHEN YEAR(CURRENT_DATE()) -  YEAR(birth_date) - (RIGHT(CURRENT_DATE(), 5) < RIGHT (birth_date, 5)) BETWEEN 12 AND 18 THEN '12-18' 
            WHEN YEAR(CURRENT_DATE()) - YEAR(birth_date) - (RIGHT(CURRENT_DATE(), 5) < RIGHT (birth_date, 5)) BETWEEN 19 AND 55 then '19-55' ELSE '56 or older' END AS age_group 
    FROM members) AS tab_age_groups
GROUP BY age_group
ORDER BY age_group;