mysql - 连接表时缺少行

Question

这个问题对我来说很熟悉，但我无法弄清楚我需要在 SQL 语句中修改什么来修复它。

简而言之，我有 2 张表 - 公司和评级。我希望所有公司都被列出，即使公司没有评级，但查询目前只抓取那些。我该如何解决这个问题？我认为使用 LEFT JOIN 而不是 INNER JOIN 就足够了，但我错了。

SELECT company.id, company.title, AVG(ratings.ratings)
FROM companies company
INNER JOIN companies_ratings ratings
ON ratings.company_id = company.id

CREATE TABLE IF NOT EXISTS `companies_ratings` (
  `id` int(4) UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` int(4) UNSIGNED NOT NULL,
  `ratings` enum('1','2','3','4','5') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `companies_ratings` (`id`, `company_id`, `ratings`) VALUES
(1, 1, '3'),
(2, 1, '5');

CREATE TABLE IF NOT EXISTS `companies` (
  `id` int(4) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `companies` (`id`, `title`) VALUES
(1, 'Company 1'),
(2, 'Company 2');

标签： mysqlinner-join

Answer 1

我写了这个有效的查询！

(SELECT company.id, company.title, AVG(ratings.ratings)
FROM companies company
left JOIN companies_ratings ratings
ON ratings.company_id = company.id)
union all
(SELECT company.id, company.title, AVG(ratings.ratings)
FROM companies company
left JOIN companies_ratings ratings
ON ratings.company_id = company.id
WHERE ratings.id is null )