php - Mysql - 如何执行此 sql

Question

我有一个“评论”表，其中包含 N 条记录和 3 个字段。

字段ID、字段名称和分数字段。

我正在尝试做的是一个查询，该查询返回按“分数”字段分组但各部分之间有值的分组。

换句话说，我想将分数从 0 到 1 的记录分组......分数从 1 到 2 的记录......依此类推，直到 5。

现在，我正在用 5 个不同的 sql 来做这件事

    Select ifnull(avg(score),0) as avg_score,count(*) as count from reviews where score between 0 and 1 and status=1;

    Select ifnull(avg(score),0) as avg_score,count(*) as count from reviews where score between 1.01 and 2 and status=1;

Answer 1

您可以按如下方式使用 FLOOR 函数:

SELECT
    CONCAT(FLOOR(SCORE), ' - ', FLOOR(SCORE) + 1) AS SCORE_RANGE, 
    IFNULL(AVG(SCORE), 0) AS AVG_SCORE,
    COUNT(*) AS COUNT
FROM
    REVIEWS
WHERE STATUS = 1 AND FLOOR(SCORE) <= 5 -- adjust value according to your requirement
GROUP BY FLOOR(SCORE);

干杯!