求一条sql语句。。想了半天，也没个头绪不知道该怎么写。我要查询DISCUZ X2.0的三个表，分别是pre_forum_thread
求一条sql语句。。
想了半天，也没个头绪不知道该怎么写。
我要查询DISCUZ X2.0的三个表，分别是pre_forum_thread，pre_forum_attachment索引表,pre_forum_attachment_n的子表
- SQL code
CREATE TABLE IF NOT EXISTS `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 DEFAULT CHARSET=utf8 AUTO_INCREMENT=380 ;
pre_forum_attachment结构如下：
- SQL code
CREATE TABLE IF NOT EXISTS `pre_forum_attachment` ( `aid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `tid` mediumint(8) unsigned NOT NULL DEFAULT '0', `pid` int(10) unsigned NOT NULL DEFAULT '0', `uid` mediumint(8) unsigned NOT NULL DEFAULT '0', `tableid` tinyint(1) unsigned NOT NULL DEFAULT '0', `downloads` mediumint(8) NOT NULL DEFAULT '0', PRIMARY KEY (`aid`), KEY `tid` (`tid`), KEY `pid` (`pid`), KEY `uid` (`uid`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=110 ;
pre_forum_attachment_n子表结构如下
- SQL code
CREATE TABLE IF NOT EXISTS `pre_forum_attachment_0` ( `aid` mediumint(8) unsigned NOT NULL, `tid` mediumint(8) unsigned NOT NULL DEFAULT '0', `pid` int(10) unsigned NOT NULL DEFAULT '0', `uid` mediumint(8) unsigned NOT NULL DEFAULT '0', `dateline` int(10) unsigned NOT NULL DEFAULT '0', `filename` varchar(255) NOT NULL DEFAULT '', `filesize` int(10) unsigned NOT NULL DEFAULT '0', `attachment` varchar(255) NOT NULL DEFAULT '', `remote` tinyint(1) unsigned NOT NULL DEFAULT '0', `description` varchar(255) NOT NULL, `readperm` tinyint(3) unsigned NOT NULL DEFAULT '0', `price` smallint(6) unsigned NOT NULL DEFAULT '0', `isimage` tinyint(1) NOT NULL DEFAULT '0', `width` smallint(6) unsigned NOT NULL DEFAULT '0', `thumb` tinyint(1) unsigned NOT NULL DEFAULT '0', `picid` mediumint(8) NOT NULL DEFAULT '0', PRIMARY KEY (`aid`), KEY `tid` (`tid`), KEY `pid` (`pid`), KEY `uid` (`uid`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;
我需要的语句是，我要获得thread表里attachment=1(也就是含有附件的)的10条内容，并且要通过关联tid对应到attachment索引表再关联到该附件所在的attachment_n(n算是一个变量)子表，然后读取attachment_n子表里isimage=1（也就是该附件为图片）的内容。
[解决办法]
select * from pre_forum_thread a, pre_forum_attachment_n b where a.tid=b.tid and a.attachment=1 and b.isimage=1 limit 10 ;
[解决办法]
首先
pre_forum_attachment_(SELECT i.tableid as n FROM `pre_forum_attachment` AS i WHERE i.tid = t.tid )
不符合语法规则
在这里 如果 SELECT i.tableid as n FROM `pre_forum_attachment` AS i WHERE i.tid = t.tid 返回的是单值数据，那么可以写作
set @t:=concat('pre_forum_attachment_', (SELECT i.tableid as n FROM `pre_forum_attachment` AS i WHERE i.tid = t.tid));
然后再
select * from `@t`
显然这只有通过存储过程来完成
[解决办法]