mysql - SQL:计算两个值之间出现的次数

Question

我很不好意思问这么琐碎的 SQL 问题，但我似乎无法找到或开发答案。

我有一列 ID，其中一些出现多次。我想计算每个 ID 出现的次数并相应地对它们进行分组。

例如:

IDs: 112 113 114 115 112 112 112

任意分组:1、2-5、>5

最终获得返回

NUMBER OCCURRENCES 
1      3 
2-5    1
6+     0

代码？

SELECT "1" as NUMBER, 
COUNT(ID) AS OCCURRENCES 
FROM TABLE WHERE OCCURRENCES = 1
UNION
SELECT "2-5" as NUMBER,
COUNT(ID) AS OCCURRENCES
FROM TABLE WHERE OCCURRENCES BETWEEN 2 AND 5
UNION
SELECT "6+" as NUMBER,
COUNT(ID) AS OCCURRENCES
FROM TABLE WHERE OCCURRENCES > 5

感谢您的帮助，

Answer 1

我认为你基本上想要一个直方图的直方图:

select cnt, count(*), min(id), max(id)
from (select id, count(*) as cnt
      from t
      group by id
     ) t
group by cnt;

您可以为此添加范围:

select (case when cnt = 1 then '1'
             when cnt <= 5 then '2-5'
             else '6+'
        end) as cnt_range, count(*), min(id), max(id)
from (select id, count(*) as cnt
      from t
      group by id
     ) t
group by cnt_range
order by min(cnt);

这不会产生 0 值。我不确定这对于您需要做的事情是否真的很重要。