CREATE TABLE `plog_articles` (
`id` int(10) unsigned NOT NULL auto_increment,
`category_id` int(10) unsigned NOT NULL default '0',
`topic` text NOT NULL,
`text` text NOT NULL,
`date` timestamp(14) NOT NULL,
`user_id` int(10) unsigned NOT NULL default '0',
`blog_id` int(10) unsigned NOT NULL default '0',
`status` enum('published','draft','deleted') NOT NULL default 'published',
`num_reads` int(10) default '0',
`properties` text NOT NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM;;INSERT INTO `plog_articles` VALUES (18,16,'欢迎加入驴窝旅游博客社区!','假如您能读到该条消息, 这意味着您申请的Blog已经开通了,即刻开始您的精彩Blog之旅吧.',20050302152440,11,12,'published',0,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (19,17,'欢迎加入驴窝旅游博客社区!','假如您能读到该条消息, 这意味着您申请的Blog已经开通了,即刻开始您的精彩Blog之旅吧.',20050302160023,12,13,'published',0,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (20,18,'欢迎加入驴窝旅游博客社区!','假如您能读到该条消息, 这意味着您申请的Blog已经开通了,即刻开始您的精彩Blog之旅吧.',20050302171046,13,14,'published',0,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (21,19,'欢迎加入驴窝旅游博客社区!','假如您能读到该条消息, 这意味着您申请的Blog已经开通了,即刻开始您的精彩Blog之旅吧.',20050303100927,14,15,'published',2,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (22,4,'Plog之争','好久之前的事情了,没想到现在又被翻出来,将我等<a href=\"http://www.dbanotes.net/archives/2005/01/aeceaeecaeae.html\" target=\"_blank\" rel=\"nofollow\">臭骂</a>一顿,心情很郁闷。<br /><br />博客公社开始时使用的代码确实是Plog 0.3的代码,那时每个博客底下用?,20050304151538,4,4,'published',16,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (23,4,'馒头猫好看,但是不能吃猫','<p><img alt=\"馒头猫好看,但是不能吃\" src=\"http://plog/gallery/13/cat.JPG\" align=\"baseline\" /></p>[@more@]',20050307102235,4,4,'published',3,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (24,15,'博客日志第一','<p align=\"center\"><font face=\"times new roman,times,serif\">博<font color=\"#0000ff\">客日</font>志</font>将来会是<font size=\"4\"><font style=\"BACKGROUND-COLOR: #ff0000\">互联</font>网</font>下一<strong><em><u><strike>个热点</strike></u></em></strong',20050307110816,10,11,'published',0,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (25,1,'111','<p>test1</p>[@more@]',20050307165355,1,1,'published',5,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (26,1,'222','test2[@more@]',20050307165314,1,1,'published',0,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (27,20,'欢迎加入驴窝旅游博客社区!','假如您能读到该条消息, 这意味着您申请的Blog已经开通了,即刻开始您的精彩Blog之旅吧.',20050307172008,16,16,'published',0,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (28,20,'亚商论坛','bbs.asiaec.com[@more@]欢迎访问<a href=\"http://bbs.asiaec.com\">http://bbs.asiaec.com</a>',20050307172147,16,16,'published',0,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (29,21,'测试一下哈','测试测试了[@more@]',20050307172412,16,16,'published',0,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (30,20,'测试预览功能','预览功能测试,怎么打字屏幕有点慌,什么问题呢?[@more@]',20050307172501,16,16,'draft',0,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (31,20,'测试预览','上次测试的是草稿,是不是需要后台审核一下呢[@more@]如题',20050307172657,16,16,'published',0,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (32,1,'特斯塔','ok[@more@]',20050307181030,1,1,'published',2,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (33,22,'还是测试','还是测试[@more@]',20050307190859,1,1,'published',11,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (34,23,'欢迎加入驴窝旅游博客社区!','假如您能读到该条消息, 这意味着您申请的Blog已经开通了,即刻开始您的精彩Blog之旅吧.',20050308141034,18,17,'published',1,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (35,23,'三八妇女节快乐','三八妇女节是国际节日![@more@]',20050308143311,18,17,'published',9,'a:1:{s:16:\"comments_enabled\";b:1;}');
`id` int(10) unsigned NOT NULL auto_increment,
`category_id` int(10) unsigned NOT NULL default '0',
`topic` text NOT NULL,
`text` text NOT NULL,
`date` timestamp(14) NOT NULL,
`user_id` int(10) unsigned NOT NULL default '0',
`blog_id` int(10) unsigned NOT NULL default '0',
`status` enum('published','draft','deleted') NOT NULL default 'published',
`num_reads` int(10) default '0',
`properties` text NOT NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM;;INSERT INTO `plog_articles` VALUES (18,16,'欢迎加入驴窝旅游博客社区!','假如您能读到该条消息, 这意味着您申请的Blog已经开通了,即刻开始您的精彩Blog之旅吧.',20050302152440,11,12,'published',0,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (19,17,'欢迎加入驴窝旅游博客社区!','假如您能读到该条消息, 这意味着您申请的Blog已经开通了,即刻开始您的精彩Blog之旅吧.',20050302160023,12,13,'published',0,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (20,18,'欢迎加入驴窝旅游博客社区!','假如您能读到该条消息, 这意味着您申请的Blog已经开通了,即刻开始您的精彩Blog之旅吧.',20050302171046,13,14,'published',0,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (21,19,'欢迎加入驴窝旅游博客社区!','假如您能读到该条消息, 这意味着您申请的Blog已经开通了,即刻开始您的精彩Blog之旅吧.',20050303100927,14,15,'published',2,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (22,4,'Plog之争','好久之前的事情了,没想到现在又被翻出来,将我等<a href=\"http://www.dbanotes.net/archives/2005/01/aeceaeecaeae.html\" target=\"_blank\" rel=\"nofollow\">臭骂</a>一顿,心情很郁闷。<br /><br />博客公社开始时使用的代码确实是Plog 0.3的代码,那时每个博客底下用?,20050304151538,4,4,'published',16,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (23,4,'馒头猫好看,但是不能吃猫','<p><img alt=\"馒头猫好看,但是不能吃\" src=\"http://plog/gallery/13/cat.JPG\" align=\"baseline\" /></p>[@more@]',20050307102235,4,4,'published',3,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (24,15,'博客日志第一','<p align=\"center\"><font face=\"times new roman,times,serif\">博<font color=\"#0000ff\">客日</font>志</font>将来会是<font size=\"4\"><font style=\"BACKGROUND-COLOR: #ff0000\">互联</font>网</font>下一<strong><em><u><strike>个热点</strike></u></em></strong',20050307110816,10,11,'published',0,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (25,1,'111','<p>test1</p>[@more@]',20050307165355,1,1,'published',5,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (26,1,'222','test2[@more@]',20050307165314,1,1,'published',0,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (27,20,'欢迎加入驴窝旅游博客社区!','假如您能读到该条消息, 这意味着您申请的Blog已经开通了,即刻开始您的精彩Blog之旅吧.',20050307172008,16,16,'published',0,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (28,20,'亚商论坛','bbs.asiaec.com[@more@]欢迎访问<a href=\"http://bbs.asiaec.com\">http://bbs.asiaec.com</a>',20050307172147,16,16,'published',0,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (29,21,'测试一下哈','测试测试了[@more@]',20050307172412,16,16,'published',0,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (30,20,'测试预览功能','预览功能测试,怎么打字屏幕有点慌,什么问题呢?[@more@]',20050307172501,16,16,'draft',0,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (31,20,'测试预览','上次测试的是草稿,是不是需要后台审核一下呢[@more@]如题',20050307172657,16,16,'published',0,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (32,1,'特斯塔','ok[@more@]',20050307181030,1,1,'published',2,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (33,22,'还是测试','还是测试[@more@]',20050307190859,1,1,'published',11,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (34,23,'欢迎加入驴窝旅游博客社区!','假如您能读到该条消息, 这意味着您申请的Blog已经开通了,即刻开始您的精彩Blog之旅吧.',20050308141034,18,17,'published',1,'a:1:{s:16:\"comments_enabled\";b:1;}');
INSERT INTO `plog_articles` VALUES (35,23,'三八妇女节快乐','三八妇女节是国际节日![@more@]',20050308143311,18,17,'published',9,'a:1:{s:16:\"comments_enabled\";b:1;}');
解决方案 »
- 关于删除sql的效率
- 求一个SQL语句。
- mysqldump语法
- (救急)mysql连接报错:ERROR 2013 (HY000): Lost connection to MySQL server during query
- SET关键设置变量, 求惑
- 如何合并MYSQL数据库
- 期待高手解释下存储过程有什么具体作用,能有例子最好
- 一个游标问题
- 请问怎么样在记录集中只取出行数为单数的记录?为什么这样不行?
- 多选框和数据库字段的问题
- 如何修改mysql的最大连接数阿,我用的是4.1.7版本 ?
- MySQL/Postgresql是开放源代码的吗?在哪儿可以下载到他们的源代码?可以对他们的源代码进行修改吗?
right JOIN plog_articles_comments AS b on c.id=b.article_id
GROUP BY b.article_id ORDER BY total_comments DESC
查询的结果是只返回了plog_articles_comments有记录的,没记录的没返回,我得想法是有记录返回,没记录返回0,而不是不返回,请问高手如何处理?
5 3 3 3 22 6
35 23 18 17 10 2
22 4 4 4 16 1
32 1 1 1 2 1
33 22 1 1 11 1
8 3 3 3 7 1
我期望的输出结果如下:
id category_id user_id blog_id num_reads total_comments
5 3 3 3 22 6
35 23 18 17 10 2
22 4 4 4 16 1
32 1 1 1 2 1
33 22 1 1 11 1
8 3 3 3 7 17 3 2 10 6 0
4 2 2 10 6 0
3 2 2 10 6 0
...也就是把id的所有值输出,把total_comments为0的也输出,怎么办?
union all SELECT c.*,'0' as total_comments FROM plog_articles as c left JOIN plog_articles_comments AS b on c.id=b.article_id where b.article_id is null;
不知道是不你要的