MySQL count、left join、group by返回零行
MySQL count、left join、group by返回零行
提问于 2013-01-27 23:55:52
在以下sql语句中：
SELECT `keywords`.keyID, count(`keywords-occurencies`.keyID) as countOccurencies
FROM `keywords-occurencies`
LEFT JOIN `keywords`
ON `keywords-occurencies`.keyID = `keywords`.keyID
WHERE `keywords-occurencies`.`keyID` IN (1,2,3) AND date BETWEEN '2013/01/25' AND '2013/01/27'
GROUP BY `keywords`.`keyID`如果keyID 3没有返回值，则不会将其计为0，也不会将其包括在结果集中，并显示如下结果
keyID countOccurencies
1 3
3 5我想像这样显示零结果
keyID countOccurencies
1 3
2 0
3 5用于测试的样本数据：
--
-- Table structure for table `keywords`
--
CREATE TABLE IF NOT EXISTS `keywords` (
`keyID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`keyName` varchar(40) NOT NULL,
PRIMARY KEY (`keyID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `keywords`
--
INSERT INTO `keywords` (`keyID`, `keyName`) VALUES
(1, 'testKey1'),
(2, 'testKey2');
-- --------------------------------------------------------
--
-- Table structure for table `keywords-occurencies`
--
CREATE TABLE IF NOT EXISTS `keywords-occurencies` (
`occurencyID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`keyID` int(10) unsigned NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`occurencyID`),
KEY `keyID` (`keyID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `keywords-occurencies`
--
INSERT INTO `keywords-occurencies` (`occurencyID`, `keyID`, `date`) VALUES
(1, 1, '2013-01-27'),
(2, 1, '2013-01-26');
--
-- Constraints for table `keywords-occurencies`
--
ALTER TABLE `keywords-occurencies`
ADD CONSTRAINT `keywords@002doccurencies_ibfk_1` FOREIGN KEY (`keyID`) REFERENCES `keywords` (`keyID`) ON DELETE CASCADE ON UPDATE CASCADE;回答 3
Stack Overflow用户
发布于 2013-01-27 23:57:58
在date:：上打个反勾
SELECT
`keywords`.keyID,
count(`keywords-occurencies`.keyID) as countOccurencies
FROM `keywords-occurencies`
LEFT JOIN `keywords` ON `keywords-occurencies`.keyID = `keywords`.keyID
WHERE `keywords-occurencies`.`keyID` IN (1,2,3) AND `date` BETWEEN '2013/01/25' AND '2013/01/27'
GROUP BY `keywords-occurencies`.`keyID`Stack Overflow用户
发布于 2013-01-28 00:05:51
有两件事。您需要按左外部联接的第一部分上的id进行分组。然后你需要计算一下第二个边是什么。对于右外部联接，顺序是相反的：
SELECT k.keyID, count(ko.keyID) as countOccurencies
FROM `keywords-occurencies` ko
RIGHT JOIN `keywords` k
ON ko.keyID = k.keyID
WHERE k.`keyID` IN (1,2,3) AND date BETWEEN '2013/01/25' AND '2013/01/27'
GROUP BY k.`keyID`这样做的原因与左外部连接有关。它将所有内容保存在第一个表中，即使没有匹配项也是如此。所以，这就是你获得完整列表的地方。至于计数，您想要计算匹配数。如果你计算第一个表中的id，你总是会得到至少1。计算第二个表中的id会得到0。
请注意，我还向表中添加了别名，以使查询更具可读性。
Stack Overflow用户
发布于 2015-05-19 15:38:32
将其更改为LEFT JOIN对我很有效：
SELECT k.keyID, count(ko.keyID) as countOccurencies
FROM `keywords-occurencies` ko
LEFT JOIN `keywords` k
ON ko.keyID = k.keyID
WHERE k.`keyID` IN (1,2,3) AND date BETWEEN '2013/01/25' AND '2013/01/27'
GROUP BY k.`keyID`页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/14549120
复制相关文章
相似问题
腾讯云开发者
