在Laravel中查询表并将结果分组到范围中

Question

我有一个数据库表，存储了多个调查分数记录，分数在1-100之间。我试图通过将分数分组到以下范围，在应用程序前端呈现频率分布；

• 小于20
• 20-30
• 30-40
• 40-50
• 50-60
• 60-70
• 70-80
• 80-90
• 90-100

因此，如果表中有数据87、92、95、98，用户将看到

• 80-90(1(
• 90-100(3(

等。我认为收集是实现这一目标的方法，但我不知道从哪里开始获得这种输出，也不知道在Laravel中是否可能？

Answer 1

是的，这是可能的。我相信这就是您需要的SQL查询(假设您的表名是"scores"，"score"是适当的字段(：

select (case when score between 0 and 20 then 'Less than 20'
when score between 21 and 30 then 'Between 21 and 30'
when score between 31 and 40 then 'Between 31 and 40'
when score between 41 and 50 then 'Between 41 and 50'
when score between 51 and 60 then 'Between 51 and 60'
when score between 61 and 70 then 'Between 61 and 70'
when score between 71 and 80 then 'Between 71 and 80'
when score between 81 and 90 then 'Between 81 and 90'
when score between 91 and 100 then 'Between 91 and 100'
end) as score_range, count(*) as count
from scores
group by score_range
order by min(score);

因此，对于Laravel来说，它可以这样工作：

$frequency = DB::select("SELECT (CASE
WHEN score BETWEEN 0 AND 20 THEN 'Less than 20'
WHEN score BETWEEN 21 AND 30 THEN '20-30'
WHEN score BETWEEN 31 AND 40 THEN '30-40'
WHEN score BETWEEN 41 AND 50 THEN '40-50'
WHEN score BETWEEN 51 AND 60 THEN '50-60'
WHEN score BETWEEN 61 AND 70 THEN '60-70'
WHEN score BETWEEN 71 AND 80 THEN '70-80'
WHEN score BETWEEN 81 AND 90 THEN '80-90'
WHEN score BETWEEN 91 AND 100 THEN '90-100'
END) AS score_range, COUNT(*) as count
FROM scores
GROUP BY score_range
ORDER BY MIN(score);");

您只需编辑文本标题即可。

在这个查询中；40-50〃；(例如(这意味着分数在41到50之间。此外，您还可以替换"；ORDER BY MIN(得分("；至"；ORDER BY计数"；如果你愿意的话。