Mysql按范围排序

Question

我有一个数据库，看起来像这样：

NUM  / CNT
3    / 1
5    / 0
100  / 1
300  / 0
320  / 1

我正在寻找一个查询，它允许我按范围对它们进行排序，并对它们的计数进行求和，所以我的结果如下所示：

NUM     / CNT
0-100   / 2
100-400 / 1

我想知道是否可以使用mysql查询。

Answer 1

对每个NUM的值使用条件聚合的SUM

SELECT '0-100' AS NUM,
       SUM(CASE WHEN NUM BETWEEN 0 AND 100 THEN CNT ELSE 0 END) AS CNT
FROM yourTable
UNION
SELECT '100-400' AS NUM,
       SUM(CASE WHEN NUM BETWEEN 100 AND 400 THEN CNT ELSE 0 END) AS CNT
FROM yourTable

Answer 2

试试这个吧，它的效果和你想要的一样。

SELECT '0-100' AS NUM,
       SUM(CASE WHEN NUM <= 100 THEN CNT ELSE 0 END) AS CNT
FROM  tableName
UNION
SELECT '100-400' AS NUM,
       SUM(CASE WHEN NUM > 100 THEN CNT ELSE 0 END) AS CNT
FROM  tableName