如何在mysql中按年龄段对用户数量进行分组 [英] How to group number of users by age bands in mysql

问题描述

基本上，我有一个mysql数据库，其中包含用户及其相应的出生日期.我还发现了以下代码，可以帮助我找到用户从出生之日起的实际年龄. 查找出生日期我需要做的是找到与众不同的年龄段"，并计算该年龄段的用户数量.我还发现了此示例，该示例确切地显示了如何进行分组这个数据.我想首先计算年龄，并以类似于以下

Essentially I have a mysql database with users and their corresponding date of birth. I have also found the following bit of code that would help me find the users actual age from the date of birth. Finding date of birth What I need to do is find different "age bands" and count the amount of users in that age band. I have also found this example that shows exactly how to group this data. I want to calculate the age first and use it in the a way similar as shown in the following link. I have written the following code and am getting an error when running it:

SELECT DATE_FORMAT(NOW(), '%Y') -
DATE_FORMAT(data_of_birth, '%Y') -
(DATE_FORMAT(NOW(), '00-%m-%d') <
DATE_FORMAT(data_of_birth,
'00-%m-%d')) AS age, COUNT(*),  
CASE  
  WHEN age >= 10 AND age <= 20 THEN '10-20'  
  WHEN age >=21 AND age <=30 THEN '21-30'  
  WHEN age >=31 AND age <=40 THEN '31-40'  
  WHEN age >=41 AND age <= 50 THEN '31-40'  
  WHEN age >=51 AND age <=60 THEN '51-60'  
  WHEN age >=61 THEN '61+'   
END AS ageband  
.. ..   
GROUP BY ageband  

我收到一条错误消息，指出未知的使用期限.我写错了吗?在case语句中写年龄的地方，我可以很容易地写出计算年龄的整个代码块，但这似乎效率很低.我还不太擅长mysql，但我知道必须有更好的方法来做到这一点.我想我的主要问题是，是否可以通过某种方式在查询中创建函数并将该函数的输出分配给值?

I get an error stating that the field age is not known. Am I writing this incorrectly? I could easily write the whole block of code that calculates age where age is written in the case statement but this seems to be very inefficient. I am not very good at mysql (yet) and I know that there has to be a better way to do this. I guess my main question is if there is some sort of way to create a function inside a query and assign the output of that function to a value?

推荐答案

在这种情况下，您可以使用子查询:

In this case you can use a subquery:

SELECT
  COUNT(*),
  CASE
    WHEN age >=10 AND age <=20 THEN '10-20'
    WHEN age >=21 AND age <=30 THEN '21-30'
    WHEN age >=31 AND age <=40 THEN '31-40'
    WHEN age >=41 AND age <=50 THEN '41-50'
    WHEN age >=51 AND age <=60 THEN '51-60'
    WHEN age >=61 THEN '61+'
  END AS ageband
FROM
  (
     DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(date_of_birth, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(date_of_birth, '00-%m-%d')) AS age,
.. ..
  ) as tbl
GROUP BY ageband;

因此，首先，它执行子查询并建立年龄表，然后再汇总年龄值.

So first it executes the subquery and builds a table of ages, than it aggregates the age value.

这篇关于如何在mysql中按年龄段对用户数量进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！