MySQL - 按范围分组

Question

我需要在值范围内计算记录数。

例如：对于集合1, 7, 9, 23, 33, 35, 1017， select count(myvalue) group by round(myvalue / 10)会得到类似这样的结果：

0-10  -> 3
10-20 -> 0
20-30 -> 1
30-40 -> 2
1010-1020 -> 1

这个方法很好用。不过，我需要设置一个上限，以便MySQL返回40+ --> 1？如何实现呢？

- thelost

Answer 1

您可以在客户端对值进行求和，也可以使用两个查询（可能带有union）来获取数据，例如：

select round(myvalue / 10), count(myvalue) from table where myvalue < 40 group by round(myvalue / 10)
union
select '40+', count(myvalue) from table where myvalue >= 40

完全可以使用子查询或复杂条件在单个查询中编写，但这样做不会像简单易维护的方式那样方便。

- pilsetnieks

---

我只是希望有一种通用的方式来定义查询的上限和下限（例如20- / 40+）。 - thelost

Answer 2

SELECT case 
   when myvalue >= 0 and myvalue< 10 then '0-9'
   when myvalue >= 10 and myvalue< 20 then '10-19'
   when myvalue >= 20 and myvalue< 20 then '20-29'
   when myvalue >= 30 and myvalue< 40 then '30-39'
   else '40+' 
   end as range
 from t 
group by range

- Zion Gabay

---

如果您能编辑您的答案并将其与MySQL联系起来，这对OP会很有帮助。 - minocha

Answer 3

select t.myvalue as [range], count(*) as [occurences]
from (
  select myvalue,
   case when myvalue >= 0 and myvalue< 10 then '0-9'
   when myvalue >= 10 and myvalue< 20 then '10-19'
   when myvalue >= 20 and myvalue< 20 then '20-29'
   when myvalue >= 30 and myvalue< 40 then '30-39'
   else '40+' end as range
 from t) t
group by t.myvalue

- Jayram

---

它没有起作用，有什么想法吗？SELECT t.salary as range, count(*) as num_employee
FROM (SELECT salary, CASE WHEN salary < = 50000 THEN '最低' WHEN salary > 50000 and salary < = 70000 THEN '中等' ELSE '最高' END AS RANGE FROM employee_salary) t GROUP BY t.salary - Mr X

Answer 4

我建议采用既有pilsetnieks和Jayram的解决方案中借鉴的方法：

```html

我建议采用既有pilsetnieks和Jayram的解决方案中借鉴的方法：

```

SELECT
    COUNT(*) AS cnt,
    IF (myvalue >= 40; -1; ROUND(myvalue / 10) AS range
FROM t
GROUP BY range

- RandomSeed

---

至少在MySQL中，“range”是一个保留字：9.3保留字。 - berliner