MYSQL查询，连接2个表的问题

Question

我有这个问题-

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

我想更改它，以便最终可以显示名为 categories 的单独表中的类别名称。我所能显示的是来自 interests 的 categoryID 和这个 sql

两个表结构都是

#interests

CREATE TABLE `interests` (
 `interestID` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) NOT NULL,
 `categoryID` int(11) NOT NULL,
 `sessionID` int(11) NOT NULL,
 `interest_desc` varchar(30) NOT NULL,
 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`interestID`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8   

类别表结构

# categories
CREATE TABLE `categories` (
 `categoryID` int(11) NOT NULL AUTO_INCREMENT,
 `category_desc` varchar(100) NOT NULL,
 PRIMARY KEY (`categoryID`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

我知道需要某种类型的连接，但我已经查看了示例并且正在努力获得准确的语法。

我在 php 脚本中有这个 - echo 语句是这样的

"{$result['interest_desc']} was the most popular in category   {$result['categoryID']}    with {$result['num_in_cat']} occurrences\n";

它的输出是这样的——

"Adidas was the most popular in category 5 with 1 occurrences"

我希望输出是“Adidas was the most popular in Sport with 1 occurrences”

但是我的 sql 查询不包含 category_desc。

Answer 1

这在性能方面更快

SELECT subsel.interest_desc, subsel.categoryID, cat.category_desc, MAX(num_in_cat) AS num_in_cat 
    FROM
    (
       SELECT interest_desc, categoryID, COUNT(categoryID) AS num_in_cat
       FROM interests
       GROUP BY interest_desc, categoryID
     ) subsel 
     inner join categories as cat on subsel.categoryID = cat.categoryID
     GROUP BY interest_desc, subsel.categoryID