mysql 我想按年龄分组，按年龄之间筛选，并对年龄之间未找到的数据显示0计数

iq0todco 于 2023-01-12 发布在 Mysql

关注(0)|答案(3)|浏览(231)

我在按年龄组显示数据时遇到了问题。以下是我的问题：

SELECT 
    CASE WHEN age BETWEEN 0 AND 18 OR age IS NULL THEN '0-18' 
         WHEN age BETWEEN 19 AND 30 THEN '19-30' 
         WHEN age BETWEEN 31 AND 35 THEN '31-35' 
         WHEN age BETWEEN 36 AND 50 THEN '36-50' 
        WHEN age BETWEEN 51 AND 100 THEN '50+' 
    END AS age_group, 
    COALESCE(COUNT(*), 0) AS count 
FROM patient_registration 
GROUP BY age_group;

我试图运行上面的查询来显示数据，你可以看到上面的查询执行没有任何错误，但我需要一个年龄组的解决方案，其中计数为0，例如。
我没有0到18岁之间的年龄记录，它不会显示在输出中，但我希望这样显示记录
| 年龄组|计数|
| - ------|- ------|
| 0-18 |无|
| 十九至三十岁|一百九十二|
| 三十一至三十五|八十三|
| 三十六至五十岁|二百二十三|
| 50岁以上|二百二十二|
如果不满足，我想显示计数0.
以下是我目前所做的尝试：

SELECT 
  CASE 
    WHEN age BETWEEN 0 AND 18 OR age COUNT is NULL THEN '0-18'
    WHEN age BETWEEN 19 AND 30 THEN '19-30'
    WHEN age BETWEEN 31 AND 35 THEN '31-35'
    WHEN age BETWEEN 36 AND 50 THEN '36-50'
    WHEN age BETWEEN 51 AND 100 THEN '50+'
  END AS age_group, 
  COALESCE(COUNT(*), NULL) AS count
FROM patient_registration
GROUP BY age_group;

有没有别的办法可以试试？

mysql

来源：https://stackoverflow.com/questions/74785734/i-want-to-group-by-age-filter-by-in-between-age-and-show-0-count-for-data-not-fo

3条答案

按热度按时间

2w2cym1i1#

这个选项可能对你有用。我返回所有的列，不管计数是多少，每一列都是有问题的年龄段。

SELECT 
      sum( case WHEN age BETWEEN 0 AND 18 
                OR age IS NULL THEN 1 else 0 end ) Age0_18, 
      sum( case WHEN age BETWEEN 19 AND 30 THEN 1 else 0 end ) Age19_30, 
      sum( case WHEN age BETWEEN 31 AND 35 THEN 1 else 0 end ) Age31_35, 
      sum( case WHEN age BETWEEN 36 AND 50 THEN 1 else 0 end ) Age36_50, 
      sum( case WHEN age > 50 THEN 1 else 0 end ) AgeOver50 
   FROM 
      patient_registration

结果会是

Age0_18   Age19_30   Age31_35   Age36_50   AgeOver50
0         192        83         223        222

赞(0）回复(0）举报 2023-01-12

kkih6yb82#

为了达到您想要的结果，您必须使用一个虚拟表，然后使用LEFT连接来生成这个结果-

SELECT age_group,
       coalesce(count(*),0) as cnt
  FROM (SELECT '0-18' age_grp
         UNION ALL
        SELECT '19-30'
         UNION ALL
        SELECT '31-35'
         UNION ALL
        SELECT '36-50'
         UNION ALL
        SELECT '50+'
      ) all_grp
  LEFT JOIN (SELECT CASE WHEN age BETWEEN 0 AND 18 OR age IS NULL THEN '0-18' 
                         WHEN age BETWEEN 19 AND 30 THEN '19-30' 
                         WHEN age BETWEEN 31 AND 35 THEN '31-35' 
                         WHEN age BETWEEN 36 AND 50 THEN '36-50' 
                         WHEN age BETWEEN 51 AND 100 THEN '50+' 
                    END AS age_group, 
                    COUNT(*) AS count 
               FROM patient_registration 
              GROUP BY age_group
              ) d ON all_grp.age_grp = d.age_group;

赞(0）回复(0）举报 2023-01-12

zkure5ic3#

SELECT age_groups.age_group,
       COUNT(*) `count`
FROM ( 
    SELECT 0 minimal, 18 maximal, '0-18' age_group
    UNION ALL
    SELECT 19, 30, '19-30'
    UNION ALL
    SELECT 31, 35, '31-35'
    UNION ALL
    SELECT 36, 50, '36-50'
    UNION ALL
    SELECT 51, 999, '50+'
    ) age_groups
LEFT JOIN patient_registration 
    ON patient_registration.age BETWEEN age_groups.minimal AND age_groups.maximal
GROUP BY 1;

查询将不计算年龄无效（负数，大于999）或未设置年龄（为NULL）的行。
此外，您可能有一个包含许多范围集的表，并使用它来代替合成表，从而使查询动态化。

赞(0）回复(0）举报 2023-01-12

我来回答

mysql 我想按年龄分组，按年龄之间筛选，并对年龄之间未找到的数据显示0计数

3条答案

相关问题

热门标签

最新问答