mysql - 在 mysql 列中找到第 n 个最常出现的值

Question

我有一张 table :

alphabet occurrence

a 22
b 22
c 21
d 12
a 22
g 20
h 11
c 22
b 32

如何使用 SQL 命令在此表中找到第三个最常出现的值。\

我正在使用类似的东西:

SELECT occurrence, COUNT(occurrence) from mytable group by occurrence order by count(occurrence) desc limit 3;

output expected:
if n = 3
c count(occurrence of c) 
c 43

Answer 1

一种方法是在您选择行时计算行数并过滤掉该选择

SELECT letter, SUM(occurances) as num_occ, @A := @A + 1 as row_count
FROM occ, (select @A := 0) t
GROUP BY letter 
HAVING row_count = 3
ORDER BY num_occ DESC;

这是一种 hacky 的方式。但由于您在表中没有 ID，因此没有太多选择

FIDDLE DEMO

编辑:在更好地理解需求之后，这里是另一种选择。

SELECT letter, SUM(occurances) as num_occ
FROM occ
GROUP BY letter 
ORDER BY num_occ DESC
limit 2, 1; -- 2 is the 3rd row position wise starting at 0 (think of an array)

FIDDLE