用了子select虽然也可以达到要求,但速度太慢了,还是不理想。我把测试的数据表贴一下,请高手试一下还有没有更好的办法。分类表: CREATE TABLE `category` ( `id` int(8) NOT NULL AUTO_INCREMENT, `category_name` varchar(16) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ---------------------------- -- Records of category -- ---------------------------- INSERT INTO `category` VALUES ('1', '国际新闻'); INSERT INTO `category` VALUES ('2', '国内新闻'); INSERT INTO `category` VALUES ('3', '行业新闻'); INSERT INTO `category` VALUES ('4', '小区新闻'); 文章表: CREATE TABLE `article` ( `id` int(8) NOT NULL AUTO_INCREMENT, `cid` int(8) DEFAULT NULL, `title` varchar(50) DEFAULT NULL, `addedtime` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ---------------------------- -- Records of article -- ---------------------------- INSERT INTO `article` VALUES ('1', '1', 'title1', '2011-11-20 21:07:35'); INSERT INTO `article` VALUES ('2', '2', 'title2', '2011-11-21 21:07:35'); INSERT INTO `article` VALUES ('3', '3', 'title3', '2011-11-21 21:07:35'); INSERT INTO `article` VALUES ('4', '1', 'title4', '2011-11-22 21:07:35'); 当前用的语句: mysql> SELECT a.*,COUNT(b.id) AS artcount -> FROM category a LEFT JOIN article b ON a.id=b.cid -> WHERE b.addedtime>='2011-10-1' -> group by a.id; 当前语句的结果: +----+---------------+----------+ | id | category_name | artcount | +----+---------------+----------+ | 1 | 国际新闻 | 2 | | 2 | 国内新闻 | 1 | | 3 | 行业新闻 | 1 | +----+---------------+----------+ 3 rows in set (0.04 sec) 想要得到以下这样的结果,就是要把所有的类名都显示出来,(在不加where下可以,但一定要加where):+----+---------------+----------+ | id | category_name | artcount | +----+---------------+----------+ | 1 | 国际新闻 | 2 | | 2 | 国内新闻 | 1 | | 3 | 行业新闻 | 1 | | 4 | 小区新闻 | 0 | +----+---------------+----------+ 4 rows in set (0.00 sec)
select a.*,artcount from category a left join ( select cid ,count(*) as artcount from article where addedtime>='2011-10-1' group by cid) b on a.cid=b.cid
FROM category a LEFT JOIN article b ON a.id=b.cid
WHERE DATE(addedtime)>='2011-10-1'
group by a.id
是B表的字段 DATE(b.addedtime)>='2011-10-1', 你的后一句话没能听明白什么意思?是在说这个情况是正常的吗?
本地是加上group的了,情况依然。
CREATE TABLE `category` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`category_name` varchar(16) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of category
-- ----------------------------
INSERT INTO `category` VALUES ('1', '国际新闻');
INSERT INTO `category` VALUES ('2', '国内新闻');
INSERT INTO `category` VALUES ('3', '行业新闻');
INSERT INTO `category` VALUES ('4', '小区新闻');
文章表:
CREATE TABLE `article` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`cid` int(8) DEFAULT NULL,
`title` varchar(50) DEFAULT NULL,
`addedtime` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of article
-- ----------------------------
INSERT INTO `article` VALUES ('1', '1', 'title1', '2011-11-20 21:07:35');
INSERT INTO `article` VALUES ('2', '2', 'title2', '2011-11-21 21:07:35');
INSERT INTO `article` VALUES ('3', '3', 'title3', '2011-11-21 21:07:35');
INSERT INTO `article` VALUES ('4', '1', 'title4', '2011-11-22 21:07:35');
当前用的语句:
mysql> SELECT a.*,COUNT(b.id) AS artcount
-> FROM category a LEFT JOIN article b ON a.id=b.cid
-> WHERE b.addedtime>='2011-10-1'
-> group by a.id;
当前语句的结果:
+----+---------------+----------+
| id | category_name | artcount |
+----+---------------+----------+
| 1 | 国际新闻 | 2 |
| 2 | 国内新闻 | 1 |
| 3 | 行业新闻 | 1 |
+----+---------------+----------+
3 rows in set (0.04 sec)
想要得到以下这样的结果,就是要把所有的类名都显示出来,(在不加where下可以,但一定要加where):+----+---------------+----------+
| id | category_name | artcount |
+----+---------------+----------+
| 1 | 国际新闻 | 2 |
| 2 | 国内新闻 | 1 |
| 3 | 行业新闻 | 1 |
| 4 | 小区新闻 | 0 |
+----+---------------+----------+
4 rows in set (0.00 sec)
from category a left join ( select cid ,count(*) as artcount from article where addedtime>='2011-10-1' group by cid) b
on a.cid=b.cid
顶版主答案~