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

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”的单独表中显示类别名称。我只能使用以下SQL显示来自“interests”的“categoryID”。请注意，两个表的结构如下：

#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是体育类别中最受欢迎的，有1个出现次数”。

然而，我的SQL查询中没有包含“category_desc”。

- DIM3NSION

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

- Maulik Vora

Answer 2

请检查这个，它会给你所需的结果。

SELECT subsel.interest_desc, 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

- Shaikh Farooque

---

我在这里遇到了错误 - 在第5行附近使用的正确语法为'int inner join categories as cat on int.categoryID = cat.categoryID GRO'。 - DIM3NSION

尝试将“AS int”替换为“AS ints”，并将所有其他的“int”也替换为“ints”。int可能是保留字。 - Robin Castlin

Answer 3

我不知道在什么现实场景下，你发布的这两个类似的查询有意义。我建议你可以直接使用以下查询：

SELECT     i.interest_desc, c.category_desc, COUNT(i.categoryID) AS num_in_cat
FROM       interests AS i
INNER JOIN categories AS c USING (categoryID)
GROUP BY   i.interest_desc, i.categoryID

- nawfal

Answer 4

SELECT * FROM interests i LEFT JOIN categories c ON i.categoryID = c.categoryID

我还没有测试它。可能存在语法错误。

- user854595