MODE是数据中出现次数最多的值，可以有ONE MODE或MANY MODES

这是两个表中的一些值 ( sqlFiddle )

create table t100(id int auto_increment primary key, value int);
create table t200(id int auto_increment primary key, value int);

insert into t100(value) values (1),
                               (2),(2),(2),
                               (3),(3),
                               (4);
insert into t200(value) values (1),
                               (2),(2),(2),
                               (3),(3),
                               (4),(4),(4);

现在，为了获得以逗号分隔列表形式返回的 MODE(S)，我对表 t100

     SELECT GROUP_CONCAT(value) as modes,occurs
     FROM
        (SELECT value,occurs FROM 
           (SELECT value,count(*) as occurs
            FROM
            T100
            GROUP BY value)T1,
        (SELECT max(occurs) as maxoccurs FROM 
            (SELECT value,count(*) as occurs
             FROM
             T100
             GROUP BY value)T2
        )T3
        WHERE T1.occurs = T3.maxoccurs)T4
      GROUP BY occurs;

和下面对表 t200 的查询(同一个查询，只是表名改变了)我在这个例子中有 2 个表，因为要表明它适用于有 1 个 MODE 和有多个 MODE 的情况模式。

     SELECT GROUP_CONCAT(value) as modes,occurs
     FROM
        (SELECT value,occurs FROM 
           (SELECT value,count(*) as occurs
            FROM
            T200
            GROUP BY value)T1,
        (SELECT max(occurs) as maxoccurs FROM 
            (SELECT value,count(*) as occurs
             FROM
             T200
             GROUP BY value)T2
        )T3
        WHERE T1.occurs = T3.maxoccurs)T4
      GROUP BY occurs;

我的问题是“有没有更简单的方法？”

我想使用 HAVING count(*) = max(count(*)) 或类似的东西来摆脱额外的连接但无法获得 HAVING 返回我想要的结果。

更新: 正如@zneak 所建议的，我可以像下面这样简化 T3:

     SELECT GROUP_CONCAT(value) as modes,occurs
     FROM
        (SELECT value,occurs FROM 
           (SELECT value,count(*) as occurs
            FROM
            T200
            GROUP BY value)T1,
        (SELECT count(*) as maxoccurs
             FROM
             T200
             GROUP BY value
             ORDER BY count(*) DESC
             LIMIT 1
        )T3
        WHERE T1.occurs = T3.maxoccurs)T4
      GROUP BY occurs;

现在有办法完全乘坐 T3 吗？ 我试过了，但由于某种原因它没有返回任何行

  SELECT value,occurs FROM  
    (SELECT value,count(*) as occurs
     FROM t200
     GROUP BY `value`)T1
  HAVING occurs=max(occurs)  

基本上我想知道是否有一种方法可以让我只需要指定一次 t100 或 t200。

更新:我找到了一种方法来指定 t100 或 t200 一次，方法是添加一个变量来设置我自己的 maxoccurs，如下所示

  SELECT GROUP_CONCAT(CASE WHEN occurs=@maxoccurs THEN value ELSE NULL END) as modes 
  FROM 
    (SELECT value,occurs,@maxoccurs:=GREATEST(@maxoccurs,occurs) as maxoccurs
     FROM (SELECT value,count(*) as occurs
           FROM t200
           GROUP BY `value`)T1,(SELECT @maxoccurs:=0)mo
     )T2

最佳答案

您非常接近最后一个查询。以下找到一个模式:

SELECT value, occurs
FROM (SELECT value,count(*) as occurs
      FROM t200
      GROUP BY `value`
      LIMIT 1
     ) T1

不过，我认为您的问题是关于多种模式的:

SELECT value, occurs
FROM (SELECT value, count(*) as occurs
      FROM t200
      GROUP BY `value`
     ) T1
WHERE occurs = (select max(occurs)
                from (select `value`, count(*) as occurs
                      from t200
                      group by `value`
                     ) t
               );

编辑:

这在几乎任何其他数据库中都容易得多。 MySQL 既不支持 with 也不支持窗口/分析函数。

您的查询(如下所示)没有按照您的预期进行:

  SELECT value, occurs  
  FROM (SELECT value, count(*) as occurs
        FROM t200
        GROUP BY `value`
       ) T1
  HAVING occurs = max(occurs) ; 

最后的 having 子句引用变量​​ occurs 但确实使用了 max(occurs)。由于使用了 max(occurs)，这是一个返回一行的聚合查询，汇总了子查询中的所有行。

变量 occurs 未用于分组。那么，MySQL使用什么值呢？它使用子查询中某一行的任意 值。这个任意值可能匹配，也可能不匹配。但是，该值仅来自一行。没有迭代。

关于mysql - 有没有更简单的方法来查找 MySQL 中某些值的 MODE(S)，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/20646875/