mysql 哪个值具有最大计数 [英] mysql which value has maximum count

问题描述

我试图从表中提取不同字段的每个不同值出现的最大次数的值.例如，如果数据集是:

I'm trying to extract from a table the value that appears the maximum number of times for each distinct value of a different field. For example, if the data set was:

a   x
a   x
a   y
b   x
b   y
c   x
c   y
c   y

查询会产生

a   x   2
b   x   1
c   y   2

我的实验使用以下内容:

My experiments use the following:

CREATE TABLE IF NOT EXISTS `maxcount` (
  `what` varchar(1) DEFAULT NULL,
  `loc` varchar(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

和

INSERT INTO `maxcount` (`what`, `loc`) VALUES
('a', 'x'),
('a', 'x'),
('a', 'y'),
('b', 'x'),
('b', 'y'),
('c', 'x'),
('c', 'y'),
('c', 'y');

第一部分很简单:

select what, loc, count(loc) howmany from maxcount group by what, loc;

我还没有弄清楚如何使用它为每个什么"获取一行，显示具有最大计数和最大值的 loc 值.

What I haven't yet figured out is how to use this to get one row for each "what" showing the value of loc that has the maximum count and value of the maximum.

解决方案不是:

select what, loc, max(howmany) from (
select what, loc, count(loc) howmany from maxcount group by what, loc)
A group by what;

因为它产生:

a x 2
b x 1
c x 2

非常感谢您的指导！

乔治

推荐答案

检查这个...为了避免引用同组结果，我创建了一个表...你应该在做完处理后删除它，或将 maxcounttemp 替换为 (SELECT what, loc, count(loc) howmany FROM maxcount GROUP BY what, loc) 作为 tblX 我试图将其设为 TEMPORARY，但您不能在子查询中使用它，如果外面的表也是一样的.

Check this out... in order to avoid referencing the same group of results, I created a table... you should delete it after doing the processing, or replace maxcounttemp with (SELECT what, loc, count(loc) howmany FROM maxcount GROUP BY what, loc) as tblX I tried to make it TEMPORARY, but you can't use it inside a subquery if the outer table is the same one.

CREATE TABLE `maxcounttemp` (
  `what` varchar(1) DEFAULT NULL,
  `loc` varchar(1) DEFAULT NULL,
  `howmany` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO maxcounttemp (
  SELECT what, loc, count(loc) howmany FROM maxcount GROUP BY what, loc
);

SELECT mct.what, mct.loc, mct.howmany
FROM maxcounttemp mct 
WHERE (mct.what, mct.howmany) IN (
  SELECT mct2.what, MAX(mct2.howmany) 
  FROM maxcounttemp mct2 
  WHERE mct2.what = mct.what 
  GROUP BY mct2.what
) GROUP BY (mct.what);

希望它有帮助...记住 bx 或 by 在此查询中同样可能...

hope it helps... keep in mind that bx or by are equally possible on this query...

这篇关于mysql 哪个值具有最大计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！