group by或having？

Question

我有一个表A，其中有一列BMI，我想找出它们中有多少在范围内，我有4个范围。

我想要得到这样的结果，我如何进行sql查询？

<18.5     ,10
18.5–24.9 ,25
25–29.9   ,19
>30       ,2

Answer 1

select case
         when bmi < 18.5 then
          '<18.5'
         when bmi > 18.5 and < 24.9 then
          '18.5–24.9'
         when bmi < 25 and > 29.9 then
          '25–29.9'
         when bmi > 30 then
          '>30'
       end as bmi_rg,
       count(*) as num_bmis
  from your_table
 group by case
            when bmi < 18.5 then
             '<18.5'
            when bmi > 18.5 and < 24.9 then
             '18.5–24.9'
            when bmi < 25 and > 29.9 then
             '25–29.9'
            when bmi > 30 then
             '>30'
          end

使用CASE语句。要显示每个组的计数，还必须按该case语句分组。

Answer 2

您可以为每个指定的范围创建联合查询

select count(*) from table where bmi < 18.5
union select count(*) from table where bmi >= 18.5 and bmi < 25
union select count(*) from table where bmi >=25 and bmi < 30
union select count(*) from table where bmi > 30

Answer 3

试试这个：

select case when bmi < 18.5 then '<18.5'
            when bmi betwen 18.5 and 24.9 then '18.5–24.9'
            when bmi betwen 25 and 29.9 then '25–29.9'
            when bmi > 30 then '>30'
       end as bmi_group,
       count(*) as grp_count
from your_table
group by case when bmi < 18.5 then '<18.5'
              when bmi betwen 18.5 and 24.9 then '18.5–24.9'
              when bmi betwen 25 and 29.9 then '25–29.9'
              when bmi > 30 then '>30'
         end

诀窍在于，当在group by中使用相同的CASE语句时，您可以基于“BMI范围”而不是实际的BMI值本身来实现分组。使用Oracle Analytic functions也可以实现同样的效果，也可以查看该选项。