SQLite 如何找到最常见的值 [英] SQLite How to find the most common occurrences of a value

问题描述

假设我有一个带有属性 X 的表 A如何找到出现次数最多的 X?(可以有多个具有相同最高出现率)

Say I have a table A with attributes X How do I find the X's with the largest occurrences? (There can be multiple that have the same highest occurrence)

即表A

 X
--
'a'
'b'
'c'
'c'
'b'

我想回来

X
--
'b'
'c'

我不能在 Sqlite 中使用关键字 ALL 所以我不知所措.

I can't use the keyword ALL in Sqlite so I'm at a loss.

我想获取每个 X 的计数，然后对其进行排序，然后以某种方式使用 ORDER BY DESC 使最大的位于顶部，然后 LIMIT 进行比较以检查第一个元组下方的值是否相等(这意味着它们同样常见)但我不确定 LIMIT 语法以及我是否可以有这样的条件

I thought of getting the counts of each X and then sorting it and then somehow using ORDER BY DESC so that the biggest is at the top and then LIMIT with a comparison to check if values below the first tuple are equal (which means they are just as common) but I'm not sure about LIMIT syntax and if I can have a condition like that

请给出提示而不是答案，有没有我可以参考的资源以便我想出办法?

Please give a hint and not the answer, are there any resources I can reference so I can figure out a way?

推荐答案

这会处理出现次数最多的多个值

This takes care of multiple values having maximum occurence

SELECT X FROM yourTable
GROUP BY X
HAVING COUNT(*) = (
                   SELECT MAX(Cnt) 
                   FROM(
                         SELECT COUNT(*) as Cnt
                         FROM yourTable
                         GROUP BY X
                        ) tmp
                    )

SQL FIDDLE

这篇关于SQLite 如何找到最常见的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！