MODE是数据中MOST次数发生的值,可以有一种模式或多种模式
这里是两个表中的一些值(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且有多个MODES的情况.
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
Gordon Linof.. 5
您与最后一个查询非常接近.以下找到一种模式:
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使用什么价值呢?它使用子查询中某个行的任意值.此任意值可能匹配,也可能不匹配.但是,该值仅来自一行.它没有迭代.
您与最后一个查询非常接近.以下找到一种模式:
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使用什么价值呢?它使用子查询中某个行的任意值.此任意值可能匹配,也可能不匹配.但是,该值仅来自一行.它没有迭代.