首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >基于两个年龄字段的MySQL年龄范围查询

问基于两个年龄字段的MySQL年龄范围查询
EN

Stack Overflow用户
提问于 2011-01-15 11:31:09
回答 2查看 1.9K关注 0票数 1

我因为这个掉了几根头发。我发现有几个在线线程有类似的问题,但并不完全是我遇到的问题,所以我仍然有点卡住了。

我正在编写一个非常复杂的查询(至少对我来说它很复杂:)

在我的ages表中,我有一个age_start和age_end字段。

例如:

代码语言:javascript
复制
age_start   |   age_end
------------------------
0      -    64
19     -     23
0      -     19
25     -     29
65     -     100
...

我试图实现的是一个在这些范围内读取的查询。

到目前为止,我想出的最好方法是将这两个字段设置为数组值,并在我的MySQL查询中执行如下操作:

输入年龄: 33

将年龄数据放入数组中。然后像这样查询:

代码语言:javascript
复制
...WHERE $age >= $age_start AND age <= $age_end...

将返回:

代码语言:javascript
复制
age_start   |   age_end
------------------------
0       -     64
25      -    29

有没有人对此有任何想法或建议或更好的方法?

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2011-01-15 11:32:24

代码语言:javascript
复制
SELECT *
  FROM ages
 WHERE 32 BETWEEN age_start AND age_end
票数 2
EN

Stack Overflow用户

发布于 2013-05-29 20:48:56

代码语言:javascript
复制
SELECT
                        CASE
                            WHEN age < 13 THEN 'Under 13'
                            WHEN age BETWEEN 13 and 15 THEN '13 - 15'
                            WHEN age BETWEEN 16 and 20 THEN '16 - 20'
                            WHEN age BETWEEN 21 and 25 THEN '21 - 25'
                            WHEN age BETWEEN 26 and 30 THEN '26 - 30'
                            WHEN age BETWEEN 31 and 35 THEN '31 - 35'
                            WHEN age BETWEEN 36 and 40 THEN '36 - 40'
                            WHEN age BETWEEN 41 and 45 THEN '41 - 45'
                            WHEN age BETWEEN 46 and 50 THEN '46 - 50'
                            WHEN age BETWEEN 51 and 55 THEN '51 - 55'
                            WHEN age BETWEEN 51 and 55 THEN '56 - 60'
                            WHEN age BETWEEN 51 and 55 THEN '61 - 65'
                            WHEN age BETWEEN 51 and 55 THEN '66 - 70'
                            WHEN age BETWEEN 51 and 55 THEN '71 - 75'
                            WHEN age BETWEEN 51 and 55 THEN '76 - 80'
                            WHEN age BETWEEN 51 and 55 THEN '81 - 85'
                            WHEN age BETWEEN 51 and 55 THEN '86 - 90'
                            WHEN age BETWEEN 51 and 55 THEN '91 - 95'
                            WHEN age BETWEEN 51 and 55 THEN '96 - 100'
                            WHEN age >= 100 THEN 'Over 100'
                            WHEN age IS NULL THEN 'Not Filled In (NULL)'
                        END as age_range,
                        COUNT(ids) AS age_count,group_concat(ids) as chart_ids

                        FROM (SELECT 
                                TIMESTAMPDIFF(YEAR, `TABLE_FIELD_OF_DATE_OF_BIRTH`, CURDATE()) AS age, user_id as ids 
                           FROM 
                                jw_user 
                            WHERE 
                                is_active=1 
                            AND 
                                is_deleted = 0 
                        ) as derived

                    GROUP BY age_range

                    ORDER BY age_range

这将是令人惊叹的:)

票数 -1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/4697792

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档