想了半天,也没个头绪不知道该怎么写。
我要查询DISCUZ X2.0的三个表,分别是pre_forum_thread,pre_forum_attachment索引表,pre_forum_attachment_n的子表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结构如下:
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子表结构如下
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(也就是该附件为图片)的内容。
我要查询DISCUZ X2.0的三个表,分别是pre_forum_thread,pre_forum_attachment索引表,pre_forum_attachment_n的子表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结构如下:
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子表结构如下
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 t.tid, a.attachment
FROM `pre_forum_thread` AS t, `pre_forum_attachment_(SELECT i.tableid as n FROM `pre_forum_attachment` AS i WHERE i.tid = t.tid )` AS a
WHERE t.attchment=1 AND a.tid = t.tid AND a.isimage=1
GROUP BY t.tid
ORDER BY t.replies DESC
LIMIT 0,10我这样写的。
但是#1103 - Incorrect table name 'pre_forum_attachment_(SELECT i.tableid as n FROM '
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`显然这只有通过存储过程来完成