数据库最常用条目查询

Question

有帮助显示结果的帮助，它没有显示结果，因为我需要他们。

我有这个

表名=电源列名= power1、power2、power2、power4、power5

 $res = mysql_query("select power1, power2, power2, power4, power5, 
 count(*) as cnt 
 from power 
 group by power1, power2, power2, power4, power5 
 order by cnt desc limit 5;");

它显示了表中的特定行，而不仅仅是表中最常见的5个数字

​

​

​

​

正如您所看到的，它显示的不是前5个数字，而是第2行。

任何想法都会受到极大的欢迎。

非常感谢

保罗

Answer 1

获取表x的列col中最常见的5个值

select col, count(*) as cnt
from x
group by col
order by cnt desc
limit 5;

如果您正在尝试获取五列中最常见的五个数字(在我的原始答案之后对问题进行了重大更改)，以下是一种方法：

select power, count(*) as cnt
from (select (case when n = 1 then power1
                   when n = 2 then power2
                   when n = 3 then power3
                   when n = 4 then power4
                   when n = 5 then power5
              end) as power
      from power p cross join
           (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5
           ) n
     ) p

这会将所有列放入一列，然后返回最常用的值。

Answer 2

要获得所有列中最常见的前5个值，您必须连接到表中5次

 Select top 5 val From
 (Select power1 val From table
  Union Select power2 val From table
  Union Select power3 val From table
  Union Select power4 val From table
  Union Select power5 val From table) z
 Group By val
 Order By Count(*) Desc

Answer 3

Select Z.Num, Count(*) As Cnt
From    (
        Select power1 As Num From power
        Union All Select power2 From power
        Union All Select power3 From power
        Union All Select power4 From power
        Union All Select power5 From power
        ) As Z
Group By Z.Num
Order By Count(*) Desc
Limit 5

确保使用Union All，否则将获得1作为每个离散数的计数。

SQL Fiddle version