Mysql 查询，基于两个字段查找最重复的条目 - Mysql query that look for most repetitive entry based on two fields

Mysql query that look for most repetitive entry based on two fields

我的数据库中有 2 行：性别（只能是：1 或 2 个"男性，女性"）、食物（各种记录，如：苹果、香蕉、吐司、意大利面等）我想将最喜欢的食物按性别划分联系起来。我做了这样的事情，但它不起作用：

$most_male = mysql_query("SELECT * FROM data WHERE sex = '1' GROUP BY foods HAVING count(*) > 2") or die(mysql_error()); //do something
$most_female= mysql_query("SELECT * FROM data WHERE sex = '2' GROUP BY foods HAVING count(*) > 2") or die(mysql_error()); //do something

此代码将仅查找> 2 的第一条记录，但不是最重复的记录。

SELECT *
FROM (SELECT sex, foods, COUNT(*)
      FROM data
      WHERE sex=1
      GROUP BY sex, food
      HAVING COUNT(*)>2
      ORDER BY COUNT(*) DESC
      LIMIT 0,1
) ilv1
UNION
SELECT *
FROM (SELECT sex, foods, COUNT(*)
      FROM data
      WHERE sex=2
      GROUP BY sex, food
      HAVING COUNT(*)>2
      ORDER BY COUNT(*) DESC
      LIMIT 0,1
) ilv2

这应该有帮助：

$most_male = mysql_query("SELECT count(*) as Count,`foods` FROM `data` WHERE `sex` = '1' GROUP BY `foods` ORDER BY `Count` DESC") or die(mysql_error());
$most_female = mysql_query("SELECT count(*) as Count,`foods` FROM `data` WHERE `sex` = '0' GROUP BY `foods` ORDER BY `Count` DESC") or die(mysql_error());