显示按体重分组的所有病人。显示每个体重组患者的总人数。按下降的权重组排序列表

Question

显示所有的病人分组为体重组。显示每个体重组患者的总人数。按权重组降序列表。

例如，如果他们的体重在100到109之间，他们被放置在100体重组，110-119 = 110体重组，等等。

Answer 1

就像这样吗？

SELECT FLOOR(t.weight/10) * 10 as weightGroup, count(*) as cnt
FROM YourTable t
GROUP BY FLOOR(t.weight/10)
ORDER BY FLOOR(t.weight/10) DESC 

Answer 2

我倾向于避免像FLOOR()或CEIL()这样昂贵的浮点函数。

将数字加到下一个较低的整数的诀窍是将其添加到0.5，然后使用CONVERT()将其硬转换为有符号整数。

WITH
--- some sample data, don't use in final query ...
indata(id,lb) AS (
          SELECT  1,101
UNION ALL SELECT  2,102 
UNION ALL SELECT  3,103 
UNION ALL SELECT  4,104 
UNION ALL SELECT  5,105 
UNION ALL SELECT  6,106 
UNION ALL SELECT  7,107 
UNION ALL SELECT  8,108 
UNION ALL SELECT  9,109 
UNION ALL SELECT 10,110 
UNION ALL SELECT 11,111 
UNION ALL SELECT 12,112 
UNION ALL SELECT 13,113 
UNION ALL SELECT 14,114 
UNION ALL SELECT 15,115 
UNION ALL SELECT 16,116 
UNION ALL SELECT 17,117 
UNION ALL SELECT 18,118 
UNION ALL SELECT 19,119 
UNION ALL SELECT 20,120 
)
-- real query starts here ...
SELECT
  CONVERT ((lb  / 10 - 0.5), SIGNED)  AS grp 
, COUNT(*) AS pat_count
FROM indata
GROUP BY grp
ORDER BY grp DESC;
-- out  grp | patcount 
-- out -----+----------
-- out   12 |        1
-- out   11 |       10
-- out   10 |        9

Answer 3

这可能会有帮助

select count (patient_id) as patient_in_group, floor (weight/10)*10 as weight_group
from patients
group by weight_group
order by weight_group desc;