我因为这个掉了几根头发。我发现有几个在线线程有类似的问题,但并不完全是我遇到的问题,所以我仍然有点卡住了。
我正在编写一个非常复杂的查询(至少对我来说它很复杂:)
在我的ages表中,我有一个age_start和age_end字段。
例如:
age_start | age_end
------------------------
0 - 64
19 - 23
0 - 19
25 - 29
65 - 100
...我试图实现的是一个在这些范围内读取的查询。
到目前为止,我想出的最好方法是将这两个字段设置为数组值,并在我的MySQL查询中执行如下操作:
输入年龄: 33
将年龄数据放入数组中。然后像这样查询:
...WHERE $age >= $age_start AND age <= $age_end...将返回:
age_start | age_end
------------------------
0 - 64
25 - 29有没有人对此有任何想法或建议或更好的方法?
发布于 2011-01-15 11:32:24
SELECT *
FROM ages
WHERE 32 BETWEEN age_start AND age_end发布于 2013-05-29 20:48:56
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这将是令人惊叹的:)
https://stackoverflow.com/questions/4697792
复制相似问题