mysql 左連接配接 group by_MySQL計數,左連接配接,group by傳回零行

在下面的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 `[email protected]_ibfk_1` FOREIGN KEY (`keyID`) REFERENCES `keywords` (`keyID`) ON DELETE CASCADE ON UPDATE CASCADE;