查找最新回复内容(字段message)的长度大于30个字符的前20个(发表时间datelin)帖子select * from comment where length(message)>30 order by datelin desc limit 20;
贴建表及插入记录的SQL,及要求结果出来看看
就是discuz的两张表主题帖子表CREATE TABLE `pre_forum_thread` ( `tid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `fid` mediumint(8) unsigned NOT NULL DEFAULT '0', `posttableid` smallint(6) unsigned NOT NULL DEFAULT '0', `typeid` smallint(6) unsigned NOT NULL DEFAULT '0', `sortid` smallint(6) unsigned NOT NULL DEFAULT '0', `readperm` tinyint(3) unsigned NOT NULL DEFAULT '0', `price` smallint(6) NOT NULL DEFAULT '0', `author` char(15) NOT NULL DEFAULT '', `authorid` mediumint(8) unsigned NOT NULL DEFAULT '0', `subject` char(80) NOT NULL DEFAULT '', `dateline` int(10) unsigned NOT NULL DEFAULT '0', `lastpost` int(10) unsigned NOT NULL DEFAULT '0', `lastposter` char(15) NOT NULL DEFAULT '', `views` int(10) unsigned NOT NULL DEFAULT '0', `replies` mediumint(8) unsigned NOT NULL DEFAULT '0', `displayorder` tinyint(1) NOT NULL DEFAULT '0', `highlight` tinyint(1) NOT NULL DEFAULT '0', `digest` tinyint(1) NOT NULL DEFAULT '0', `rate` tinyint(1) NOT NULL DEFAULT '0', `special` tinyint(1) NOT NULL DEFAULT '0', `attachment` tinyint(1) NOT NULL DEFAULT '0', `moderated` tinyint(1) NOT NULL DEFAULT '0', `closed` mediumint(8) unsigned NOT NULL DEFAULT '0', `stickreply` tinyint(1) unsigned NOT NULL DEFAULT '0', `recommends` smallint(6) NOT NULL DEFAULT '0', `recommend_add` smallint(6) NOT NULL DEFAULT '0', `recommend_sub` smallint(6) NOT NULL DEFAULT '0', `heats` int(10) unsigned NOT NULL DEFAULT '0', `status` smallint(6) unsigned NOT NULL DEFAULT '0', `isgroup` tinyint(1) NOT NULL DEFAULT '0', `favtimes` mediumint(8) NOT NULL DEFAULT '0', `sharetimes` mediumint(8) NOT NULL DEFAULT '0', `stamp` tinyint(3) NOT NULL DEFAULT '-1', `icon` tinyint(3) NOT NULL DEFAULT '-1', `pushedaid` mediumint(8) NOT NULL DEFAULT '0', `cover` smallint(6) NOT NULL DEFAULT '0', `replycredit` smallint(6) NOT NULL DEFAULT '0', PRIMARY KEY (`tid`), KEY `digest` (`digest`), KEY `sortid` (`sortid`), KEY `displayorder` (`fid`,`displayorder`,`lastpost`), KEY `typeid` (`fid`,`typeid`,`displayorder`,`lastpost`), KEY `recommends` (`recommends`), KEY `heats` (`heats`), KEY `authorid` (`authorid`), KEY `isgroup` (`isgroup`,`lastpost`), KEY `special` (`special`) ) ENGINE=MyISAM AUTO_INCREMENT=11038 DEFAULT CHARSET=utf8 回复表 CREATE TABLE `pre_forum_post` ( `pid` int(10) unsigned NOT NULL AUTO_INCREMENT, `fid` mediumint(8) unsigned NOT NULL DEFAULT '0', `tid` mediumint(8) unsigned NOT NULL DEFAULT '0', `first` tinyint(1) NOT NULL DEFAULT '0', `author` varchar(50) NOT NULL DEFAULT '', `authorid` mediumint(8) unsigned NOT NULL DEFAULT '0', `subject` varchar(80) NOT NULL DEFAULT '', `dateline` int(10) unsigned NOT NULL DEFAULT '0', `message` mediumtext NOT NULL, `useip` varchar(15) NOT NULL DEFAULT '', `invisible` tinyint(1) NOT NULL DEFAULT '0', `anonymous` tinyint(1) NOT NULL DEFAULT '0', `usesig` tinyint(1) NOT NULL DEFAULT '0', `htmlon` tinyint(1) NOT NULL DEFAULT '0', `bbcodeoff` tinyint(1) NOT NULL DEFAULT '0', `smileyoff` tinyint(1) NOT NULL DEFAULT '0', `parseurloff` tinyint(1) NOT NULL DEFAULT '0', `attachment` tinyint(1) NOT NULL DEFAULT '0', `rate` smallint(6) NOT NULL DEFAULT '0', `ratetimes` tinyint(3) unsigned NOT NULL DEFAULT '0', `status` tinyint(1) NOT NULL DEFAULT '0', `tags` varchar(255) NOT NULL DEFAULT '0', `comment` tinyint(1) NOT NULL DEFAULT '0', `replycredit` smallint(6) NOT NULL DEFAULT '0', PRIMARY KEY (`pid`), KEY `fid` (`fid`), KEY `authorid` (`authorid`), KEY `dateline` (`dateline`), KEY `invisible` (`invisible`), KEY `displayorder` (`tid`,`invisible`,`dateline`), KEY `first` (`tid`,`first`) ) ENGINE=MyISAM AUTO_INCREMENT=49964 DEFAULT CHARSET=utf8 想要的结果: 主页显示最新20条帖子的标题,这20个帖子的最新回复内容长度都大于30,小于30的不显示。
select * from post C ,( select * from commment A where length(message)>30 not exists (select 1 from comment B where A.帖子号=B.帖子号 and A.评论时间 < B.评论时间 a) )D where C.帖子号=D.帖子号 order by 发帖时间 desc limit 20
`tid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`fid` mediumint(8) unsigned NOT NULL DEFAULT '0',
`posttableid` smallint(6) unsigned NOT NULL DEFAULT '0',
`typeid` smallint(6) unsigned NOT NULL DEFAULT '0',
`sortid` smallint(6) unsigned NOT NULL DEFAULT '0',
`readperm` tinyint(3) unsigned NOT NULL DEFAULT '0',
`price` smallint(6) NOT NULL DEFAULT '0',
`author` char(15) NOT NULL DEFAULT '',
`authorid` mediumint(8) unsigned NOT NULL DEFAULT '0',
`subject` char(80) NOT NULL DEFAULT '',
`dateline` int(10) unsigned NOT NULL DEFAULT '0',
`lastpost` int(10) unsigned NOT NULL DEFAULT '0',
`lastposter` char(15) NOT NULL DEFAULT '',
`views` int(10) unsigned NOT NULL DEFAULT '0',
`replies` mediumint(8) unsigned NOT NULL DEFAULT '0',
`displayorder` tinyint(1) NOT NULL DEFAULT '0',
`highlight` tinyint(1) NOT NULL DEFAULT '0',
`digest` tinyint(1) NOT NULL DEFAULT '0',
`rate` tinyint(1) NOT NULL DEFAULT '0',
`special` tinyint(1) NOT NULL DEFAULT '0',
`attachment` tinyint(1) NOT NULL DEFAULT '0',
`moderated` tinyint(1) NOT NULL DEFAULT '0',
`closed` mediumint(8) unsigned NOT NULL DEFAULT '0',
`stickreply` tinyint(1) unsigned NOT NULL DEFAULT '0',
`recommends` smallint(6) NOT NULL DEFAULT '0',
`recommend_add` smallint(6) NOT NULL DEFAULT '0',
`recommend_sub` smallint(6) NOT NULL DEFAULT '0',
`heats` int(10) unsigned NOT NULL DEFAULT '0',
`status` smallint(6) unsigned NOT NULL DEFAULT '0',
`isgroup` tinyint(1) NOT NULL DEFAULT '0',
`favtimes` mediumint(8) NOT NULL DEFAULT '0',
`sharetimes` mediumint(8) NOT NULL DEFAULT '0',
`stamp` tinyint(3) NOT NULL DEFAULT '-1',
`icon` tinyint(3) NOT NULL DEFAULT '-1',
`pushedaid` mediumint(8) NOT NULL DEFAULT '0',
`cover` smallint(6) NOT NULL DEFAULT '0',
`replycredit` smallint(6) NOT NULL DEFAULT '0',
PRIMARY KEY (`tid`),
KEY `digest` (`digest`),
KEY `sortid` (`sortid`),
KEY `displayorder` (`fid`,`displayorder`,`lastpost`),
KEY `typeid` (`fid`,`typeid`,`displayorder`,`lastpost`),
KEY `recommends` (`recommends`),
KEY `heats` (`heats`),
KEY `authorid` (`authorid`),
KEY `isgroup` (`isgroup`,`lastpost`),
KEY `special` (`special`)
) ENGINE=MyISAM AUTO_INCREMENT=11038 DEFAULT CHARSET=utf8
回复表
CREATE TABLE `pre_forum_post` (
`pid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`fid` mediumint(8) unsigned NOT NULL DEFAULT '0',
`tid` mediumint(8) unsigned NOT NULL DEFAULT '0',
`first` tinyint(1) NOT NULL DEFAULT '0',
`author` varchar(50) NOT NULL DEFAULT '',
`authorid` mediumint(8) unsigned NOT NULL DEFAULT '0',
`subject` varchar(80) NOT NULL DEFAULT '',
`dateline` int(10) unsigned NOT NULL DEFAULT '0',
`message` mediumtext NOT NULL,
`useip` varchar(15) NOT NULL DEFAULT '',
`invisible` tinyint(1) NOT NULL DEFAULT '0',
`anonymous` tinyint(1) NOT NULL DEFAULT '0',
`usesig` tinyint(1) NOT NULL DEFAULT '0',
`htmlon` tinyint(1) NOT NULL DEFAULT '0',
`bbcodeoff` tinyint(1) NOT NULL DEFAULT '0',
`smileyoff` tinyint(1) NOT NULL DEFAULT '0',
`parseurloff` tinyint(1) NOT NULL DEFAULT '0',
`attachment` tinyint(1) NOT NULL DEFAULT '0',
`rate` smallint(6) NOT NULL DEFAULT '0',
`ratetimes` tinyint(3) unsigned NOT NULL DEFAULT '0',
`status` tinyint(1) NOT NULL DEFAULT '0',
`tags` varchar(255) NOT NULL DEFAULT '0',
`comment` tinyint(1) NOT NULL DEFAULT '0',
`replycredit` smallint(6) NOT NULL DEFAULT '0',
PRIMARY KEY (`pid`),
KEY `fid` (`fid`),
KEY `authorid` (`authorid`),
KEY `dateline` (`dateline`),
KEY `invisible` (`invisible`),
KEY `displayorder` (`tid`,`invisible`,`dateline`),
KEY `first` (`tid`,`first`)
) ENGINE=MyISAM AUTO_INCREMENT=49964 DEFAULT CHARSET=utf8
想要的结果:
主页显示最新20条帖子的标题,这20个帖子的最新回复内容长度都大于30,小于30的不显示。
select * from commment A
where length(message)>30 not exists (select 1 from comment B where A.帖子号=B.帖子号 and A.评论时间 < B.评论时间 a)
)D
where C.帖子号=D.帖子号
order by 发帖时间 desc
limit 20