表结构
mysql> desc class;
+-----------+------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------+------+-----+-------------------+-----------------------------+
| classid | int(11) | NO | PRI | NULL | |
| classname | char(64) | NO | | NULL | |
| arpic | char(255) | NO | | NULL | |
| status | tinyint(1) | NO | | NULL | |
| ctime | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-----------+------------+------+-----+-------------------+-----------------------------+
mysql> desc blog;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| uid | int(11) | NO | | NULL | |
| classid | tinyint(1) | NO | | NULL | |
| subject | char(128) | NO | | NULL | |
| datetime | int(10) | NO | | 0 | |
+------------+--------------+------+-----+---------+----------------+
21 rows in set要求:求出分类和分类的日志数
select c.classid, c.classname, count(b.id) from class c left join blog b on c.classid = b.classid where c.status=1 and b.status=1;为什么在blog里面只有一种分类的时候(所有的classid=1),只能select出class中在blog中有记录的数, 失去了左连接的含义。麻烦请教大家一下
mysql> desc class;
+-----------+------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------+------+-----+-------------------+-----------------------------+
| classid | int(11) | NO | PRI | NULL | |
| classname | char(64) | NO | | NULL | |
| arpic | char(255) | NO | | NULL | |
| status | tinyint(1) | NO | | NULL | |
| ctime | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-----------+------------+------+-----+-------------------+-----------------------------+
mysql> desc blog;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| uid | int(11) | NO | | NULL | |
| classid | tinyint(1) | NO | | NULL | |
| subject | char(128) | NO | | NULL | |
| datetime | int(10) | NO | | 0 | |
+------------+--------------+------+-----+---------+----------------+
21 rows in set要求:求出分类和分类的日志数
select c.classid, c.classname, count(b.id) from class c left join blog b on c.classid = b.classid where c.status=1 and b.status=1;为什么在blog里面只有一种分类的时候(所有的classid=1),只能select出class中在blog中有记录的数, 失去了左连接的含义。麻烦请教大家一下
因为是公司的数据库表结构,所有稍微修改了一下。
但是不影响描述的问题。
麻烦大家帮忙看下。
怎么能求出所有的分类, 和每一种分类的日志总数
估计是WHERE的问题
select c.classid, c.classname, count(b.id) from
(select * from class where status=1 ) c left join
(select * from blog where status=1) b on c.classid = b.classid
SET FOREIGN_KEY_CHECKS=0;
----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`classid` int(11) NOT NULL,
`classname` char(64) COLLATE utf8_unicode_ci NOT NULL,
`arpic` char(255) COLLATE utf8_unicode_ci NOT NULL,
`status` tinyint(1) NOT NULL,
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`classid`),
KEY `idx_cid` (`classid`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `class` VALUES ('1', '诗歌', '6.jpg', '1', '2011-08-25 09:57:35');
INSERT INTO `class` VALUES ('2', '散文', '5.jpg', '1', '2011-08-25 09:57:59');
INSERT INTO `class` VALUES ('3', '故事小说', '4.jpg', '1', '2011-08-25 09:58:12');
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for blog
-- ----------------------------
DROP TABLE IF EXISTS `blog`;
CREATE TABLE `blog` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`classid` int(10) NOT NULL,
`subject` char(128) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `blog` VALUES ('1', '1', '静夜思');
INSERT INTO `blog` VALUES ('2', '1', '回家');
INSERT INTO `blog` VALUES ('3', '1', '倔强');
INSERT INTO `blog` VALUES ('4', '2', '你好');要求结果是:
classid classname count(blog)
1 诗歌 3
2 散文 1
3 故事小说 0
+---------+-----------+-------+--------+---------------------+
| classid | classname | arpic | status | ctime |
+---------+-----------+-------+--------+---------------------+
| 1 | 诗歌 | 6.jpg | 1 | 2011-08-25 09:57:35 |
| 2 | 散文 | 5.jpg | 1 | 2011-08-25 09:57:59 |
| 3 | 故事小说 | 4.jpg | 1 | 2011-08-25 09:58:12 |
+---------+-----------+-------+--------+---------------------+
3 rows in set (0.00 sec)mysql> SELECT * FROM BLOG;
+----+---------+---------+
| id | classid | subject |
+----+---------+---------+
| 1 | 1 | 静夜思 |
| 2 | 1 | 回家 |
| 3 | 1 | 倔强 |
| 4 | 2 | 你好 |
+----+---------+---------+
4 rows in set (0.00 sec)mysql> SELECT a.`classid`,a.`classname`,COUNT(b.classid) FROM `class` a
-> LEFT JOIN blog b ON a.`classid`=b.`classid`
-> WHERE a.`status`=1 GROUP BY a.`classid`,a.`classname`;
+---------+-----------+------------------+
| classid | classname | COUNT(b.classid) |
+---------+-----------+------------------+
| 1 | 诗歌 | 3 |
| 2 | 散文 | 1 |
| 3 | 故事小说 | 0 |
+---------+-----------+------------------+
3 rows in set (0.00 sec)mysql>
+---------+-----------+-------------+
| classid | classname | count(b.id) |
+---------+-----------+-------------+
| 1 | 诗歌 | 3 |
| 2 | 散文 | 1 |
| 3 | 故事小说 | 0 |
+---------+-----------+-------------+
谢谢,能推荐一下mysql 比较好的学习资料么
希望是中文的。