在下面的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;