MYSQL查询,加入2表的问题

我有这个查询 – @H_502_2@

@H_502_2@

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

我想更改它,以便最终可以从名为categories的单独表中显示类别名称.我可以显示的只是来自这个sql的兴趣的categoryID@H_502_2@

两个表结构都是@H_502_2@

@H_502_2@

#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

类别表结构@H_502_2@

@H_502_2@

# 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

我知道需要某种类型的连接,但我已经查看了示例并且正在努力获得确切的语法.@H_502_2@

我在PHP脚本中有这个 – echo语句是这样的@H_502_2@

@H_502_2@

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

它的输出是这个 – @H_502_2@

@H_502_2@

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

我希望输出为“阿迪达斯是体育界最受欢迎的一次出现”@H_502_2@

但是我的SQL查询没有category_desc.@H_502_2@

最佳答案

这是更快速的性能@H_502_2@

@H_502_2@

SELECT subsel.interest_desc,subsel.categoryID,cat.category_desc,MAX(num_in_cat) AS num_in_cat 
    FROM
    (
       SELECT interest_desc,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

MYSQL查询,加入2表的问题

猜你在找的MySQL相关文章