php - 在 MySql 中使用年龄单位按年龄范围排序

Question

这是我的 table :

Id   User       Event    Age     Age_Unit
1    Jonathan   aaa      5       2 
2    Frank      bbb      15      1
3    Karen      ccc      42      1
4    Yamile     ddd      20      3

我需要按年龄范围分组:

0-4岁

5-10年

11-15岁

16-20岁

21-25岁

26-30岁

...等等

拥有 Age_Unit:

1 = 年

2 = 月

3 = 天

决赛 table 为

    Id   User       Event    Age     Age_Unit  Range
    1    Jonathan   aaa      5       2         0-1
    2    Frank      bbb      15      1         11-15
    3    Karen      ccc      42      1         40-45
    4    Yamile     ddd      20      3         0-1

我正在使用此查询:

SELECT `Age`, `Age_Unit`, 
CASE WHEN `Age_Unit` > 1 THEN "0-1"
WHEN `Age_Unit` = 1 THEN "Older than 1" 
ELSE NULL END AS Age_Range
FROM table1 as fe

我不知道如何整合所有年龄段。

Answer 1

例如:

SELECT x.id
     , x.user
     , x.event
     , x.age
     , x.age_unit
     , CASE WHEN years BETWEEN  0 AND 4 THEN '0-4'
                  WHEN years BETWEEN  5 AND 10 THEN '5-10'
                  WHEN years BETWEEN 11 AND 15 THEN '11-15'
                  WHEN years BETWEEN 16 AND 20 THEN '16-20'
                  WHEN years BETWEEN 21 AND 25 THEN '21-15'
                  WHEN years BETWEEN 26 AND 30 THEN '26-30'
                  ELSE 'old' END `range`
  FROM
     ( SELECT *
            , CASE age_unit 
              WHEN 3 
              THEN age/365 
              WHEN 2 
              THEN age/12 
              ELSE age 
              END years 
         FROM my_table
     ) x;