mysql根据不同年龄组的BMI值判断是否肥胖语句

有年龄和BMI值，根据不同年龄和性别的BMI标准值每个人是否肥胖进行判断，并将判断结果以数字表示添加到是否肥胖的列中

表结构发一下，以及测试数据。

方法一(如果只针某个小范围年龄段的人可以这样,如果年龄全覆盖建议采用方法二)

select 
    sex,age,bmi,
    case 
        -- 1岁的男生(后面的bmi阈值根据对应的性别与年龄修改)
        when sex='9001001' and age=1 and bmi >17.7 then 3
        when sex='9001001' and age=1 and bmi >=16.4 then 2
        when sex='9001001' and age=1 and bmi <16.4 then 1
        -- 1岁的女生
        when sex='9001002' and age=1 and bmi >17.7 then 3
        when sex='9001002' and age=1 and bmi >=16.4 then 2
        when sex='9001002' and age=1 and bmi <16.4 then 1
        -- 2岁的男生
        when sex='9001001' and age=2 and bmi >17.7 then 3
        when sex='9001001' and age=2 and bmi >=16.4 then 2
        when sex='9001001' and age=2 and bmi <16.4 then 1
        -- 2岁的女生
        when sex='9001002' and age=2 and bmi >17.7 then 3
        when sex='9001002' and age=2 and bmi >=16.4 then 2
        when sex='9001002' and age=2 and bmi <16.4 then 1
        -- 依次列举...
        -- 17岁的男生
        when sex='9001001' and age=17 and bmi >17.7 then 3
        when sex='9001001' and age=17 and bmi >=16.4 then 2
        when sex='9001001' and age=17 and bmi <16.4 then 1
        -- 17岁的女生
        when sex='9001002' and age=17 and bmi >17.7 then 3
        when sex='9001002' and age=17 and bmi >=16.4 then 2
        when sex='9001002' and age=17 and bmi <16.4 then 1
        -- 18岁以上的男生
        when sex='9001001' and age>=18 and bmi >17.7 then 3
        when sex='9001001' and age>=18 and bmi >=16.4 then 2
        when sex='9001001' and age>=18 and bmi <16.4 then 1
        -- 18岁以上的女生
        when sex='9001002' and age>=18 and bmi >17.7 then 3
        when sex='9001002' and age>=18 and bmi >=16.4 then 2
        when sex='9001002' and age>=18 and bmi <16.4 then 1
    end as is_fat
from table
;

方法二:创建一个bmi的码表,存放每个年龄+性别+阈值1(v1)+阈值2(v2)

select t1.sex,t1.age,t1.bmi,
    case 
        when t1.bmi > t2.v3 then 3
        when t1.bmi >= t2.v2 then 2
        when t1.bmi < t2.v2 then 1
    end as is_fat
from t1
join t2 on t1.sex = t2.sex and t1.age=t2.age

年龄和性别分类不久四种嘛 case when 结果存 then 里

  • [ ]

    img