计算重复条目并根据类别对它们进行排序

Question

只需要一些帮助来了解如何使用 SQL 语句来检查名为interest_desc 的列中的重复条目，然后根据它们分配到的类别 ID 对它们进行排序。

完美的结果将是和 echo 一样；

“钓鱼”是类别“4”中最受欢迎的兴趣，

努力知道从哪里开始：S

更新 - 感谢您的帮助，我现在有了这个，但我相信我的语法是错误的？

$interestCatPopular = "SELECT interest_desc, categoryID, MAX(num_in_cat) AS num_in_cat    FROM
(
SELECT 
interest_desc,
categoryID,
COUNT(categoryID) AS num_in_cat
FROM tbl
GROUP BY interest_desc, categoryID
) subsel 
GROUP BY interest_desc, categoryID";

$mostPopularInterest = mysql_query($interestCatPopular) or die(mysql_error());

$arrayResults = mysql_fetch_assoc($mostPopularInterest);
foreach ($arrayResults as $result) {
echo "{$result['interest_desc']} was the most popular in category   {$result['categoryID']} with {$result['num_in_cat']} occurrences\n";
}

:)

Answer 1

SELECT a.* 
FROM 
(
   SELECT category, interest_desc, COUNT(*) as num
   FROM category_table
   GROUP BY category,interest_desc
)a
INNER JOIN 
(
 SELECT category, MAX(num) AS max_num 
 FROM 
 (
   SELECT category, interest_desc, COUNT(*) as num
   FROM category_table
   GROUP BY category,interest_desc
 )b
 GROUP BY b.category
)c ON c.category = a.category AND a.num = c.max_num

Answer 2

使用子查询，您可以首先统计每个类别的数量。由此，外部查询选择最大计数以及相应的 categoryID 和 interest_desc。

此查询应该为每个 categoryID 生成最流行的 interest_desc，以及它出现的次数。当然，这是未经测试的。

SELECT interest_desc, categoryID, MAX(num_in_cat) AS num_in_cat FROM
(
SELECT 
  interest_desc,
  categoryID,
  COUNT(categoryID) AS num_in_cat
FROM tbl
GROUP BY interest_desc, categoryID
) subsel 
GROUP BY interest_desc, categoryID

在 PHP 中，成功查询并将 mysql_fetch_assoc() 循环调用到数组 $results 中：

更新

您需要在循环中获取结果：

// Load $arrayResults in a loop...
$arrayResults = array();
while ($row = mysql_fetch_assoc($mostPopularInterest)) {
  $arrayResults[] = $row;
}

foreach ($arrayResults as $result) {
  echo "{$result['interest_desc']} was the most popular in category   {$result['categoryID']} with {$result['num_in_cat']} occurrences\n";
}