SQL:两个值之间的次数计数

Question

问这样一个琐碎的SQL问题让我很尴尬，但我似乎找不到也找不到答案。

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

例如：

编号: 112 113 114 115 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值。我不确定这对你需要做什么是否真的很重要。

Answer 2

只是根据你对任务的描述，这应该让你开始工作。

SELECT [ID], COUNT([ID]) AS OCCURRENCES FROM t GROUP BY [ID], ORDER BY OCCURRENCES DESC

这将生成所有ID的列表以及与ID关联的事件的计数，并按降序排列。我走了这条路，因为你提到范围随意。