SQL查詢，分組依據和出現次數

我有下表（只有一列）：

X
----
1
1
1
2
2
3
3
3
4
4
5
6
6
7
7
7

我需要一個 SQL 查詢，它將輸出以下內容：

Occurrences | Number
1  1  * there are only 1 entry with one occurrence (number 5)
2  3  * there are three entries with two occurrences (numbers 2,4,6)
3  2  * there are two entries with three occurrences (numbers 1 and 7)

如果我做這樣的事情：

select count(*) FROM (SELECT count(*) as N FROM table 1 GROUP BY x) a GROUP BY a.N;

我得到了正確的結果，但只有第二列 t 帶有出現次數。有誰知道如何獲得上面範例中的完整輸出？

這應該有效：

select count(*), a.N FROM (SELECT count(*) as N FROM table1 GROUP BY x) a GROUP BY a.N;

SQLFiddle

請注意，對於 N = 3，它為您提供3，因為實際上有三個數字重複三次：1、3 和 7。

這是您可能需要的查詢：

select t.count as Occurrences, count(*) as Number from
(select a, count(*) as count from table_1 group by a)t 
group by t.count 
order by Occurrences;

引用自：https://dba.stackexchange.com/questions/218385