discuz论坛的获取最新帖子以及该帖子所属论坛的名字。
已知条件fid,涉及表cdb_forums,cdb_threads,要获得的东西,该论坛论坛ID为fid的,加父论坛的ID为fid,或者父父论坛的ID为fid的里面的所有的帖子中的最新的 帖子列表,我写了几个都不行啊。示例:已知fid=86我用的SQL语句如下:SELECT t.*,f.*
FROM cdb_threads t
LEFT JOIN cdb_forums f ON t.fid=f.fid
LEFT JOIN cdb_forums gf ON gf.fid=t.fid AND gf.type='group'
LEFT JOIN cdb_forums ff ON ff.fid=t.fid AND ff.type='forum'
LEFT JOIN cdb_forums sf ON sf.fid=t.fid AND sf.type='sub'
WHERE ((sf.fid='86' OR ff.fid='86') OR gf.fid='86')
ORDER BY t.lastpost DESC
LIMIT 0 , 24 ;表的设计:
mysql> desc cdb_threads;
+-----------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------------+------+-----+---------+----------------+
| tid | mediumint(8) unsigned | NO | PRI | NULL | auto_increment |
| fid | smallint(6) unsigned | NO | MUL | 0 | |
| iconid | smallint(6) unsigned | NO | | 0 | |
| typeid | smallint(6) unsigned | NO | | 0 | |
| sortid | smallint(6) unsigned | NO | MUL | 0 | |
| readperm | tinyint(3) unsigned | NO | | 0 | |
| price | smallint(6) | NO | | 0 | |
| author | char(15) | NO | | | |
| authorid | mediumint(8) unsigned | NO | MUL | 0 |
| subject | char(80) | NO | | | |
| dateline | int(10) unsigned | NO | | 0 | |
| lastpost | int(10) unsigned | NO | | 0 | |
| lastposter | char(15) | NO | | | |
| views | int(10) unsigned | NO | | 0 | |
| replies | mediumint(8) unsigned | NO | | 0 | |
| displayorder | tinyint(1) | NO | | 0 | |
| highlight | tinyint(1) | NO | | 0 | |
| digest | tinyint(1) | NO | MUL | 0 | |
| rate | tinyint(1) | NO | | 0 | |
| special | tinyint(1) | NO | | 0 | |
| attachment | tinyint(1) | NO | | 0 | |
| moderated | tinyint(1) | NO | | 0 | |
| closed | mediumint(8) unsigned | NO | | 0 | |
| itemid | mediumint(8) unsigned | NO | | 0 | |
| supe_pushstatus | tinyint(1) | NO | | 0 | |
| recommends | smallint(6) | NO | MUL | | |
| recommend_add | smallint(6) | NO | | | |
| recommend_sub | smallint(6) | NO | | | |
| heats | int(10) unsigned | NO | MUL | 0 | |
+-----------------+-----------------------+------+-----+---------+----------------+
29 rows in set (0.03 sec)
mysql> desc cdb_forums;
+------------------+-----------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-----------------------------+------+-----+---------+----------------+
| fid | smallint(6) unsigned | NO | PRI | NULL | auto_increment |
| fup | smallint(6) unsigned | NO | MUL | 0 | |
| type | enum('group','forum','sub') | NO | | forum | |
| name | char(50) | NO | | | |
| status | tinyint(1) | NO | MUL | 0 | |
| displayorder | smallint(6) | NO | | 0 | |
| styleid | smallint(6) unsigned | NO | | 0 | |
| threads | mediumint(8) unsigned | NO | | 0 | |
| posts | mediumint(8) unsigned | NO | | 0 | |
| todayposts | mediumint(8) unsigned | NO | | 0 | |
| lastpost | char(110) | NO | | | |
| allowsmilies | tinyint(1) | NO | | 0 | |
| allowhtml | tinyint(1) | NO | | 0 | |
| allowbbcode | tinyint(1) | NO | | 0 | |
| allowimgcode | tinyint(1) | NO | | 0 | |
| allowmediacode | tinyint(1) | NO | | 0 | |
| allowanonymous | tinyint(1) | NO | | 0 | |
| allowshare | tinyint(1) | NO | | 0 | |
| allowpostspecial | smallint(6) unsigned | NO | | 0 | |
| allowspecialonly | tinyint(1) unsigned | NO | | 0 | |
| alloweditrules | tinyint(1) | NO | | 0 | |
| allowfeed | tinyint(1) | NO | | 1 | |
| recyclebin | tinyint(1) | NO | | 0 | |
| modnewposts | tinyint(1) | NO | | 0 | |
| jammer | tinyint(1) | NO | | 0 | |
| disablewater | tinyint(1) | NO | | 0 | |
| inheritedmod | tinyint(1) | NO | | 0 | |
| autoclose | smallint(6) | NO | | 0 | |
| forumcolumns | tinyint(3) unsigned | NO | | 0 | |
| threadcaches | tinyint(1) | NO | | 0 | |
| alloweditpost | tinyint(1) unsigned | NO | | 1 | |
| simple | tinyint(1) unsigned | NO | | | |
| modworks | tinyint(1) unsigned | NO | | | |
| allowtag | tinyint(1) | NO | | 1 | |
| allowglobalstick | tinyint(1) | NO | | 1 | |
+------------------+-----------------------------+------+-----+---------+----------------+
35 rows in set (0.03 sec)forum中的类型有三种,(表forum中的字段type)
1.group -- 大论坛
2.forum -- 论坛
3.sub -- 子论坛。===============================================================================================
如何才可以选出,只已知一个fid,
已知条件fid,涉及表cdb_forums,cdb_threads,要获得的东西,该论坛论坛ID为fid的,加父论坛的ID为fid,或者父父论坛的ID为fid的里面的所有的帖子中的最新的 帖子列表,我写了几个都不行啊。示例:已知fid=86我用的SQL语句如下:SELECT t.*,f.*
FROM cdb_threads t
LEFT JOIN cdb_forums f ON t.fid=f.fid
LEFT JOIN cdb_forums gf ON gf.fid=t.fid AND gf.type='group'
LEFT JOIN cdb_forums ff ON ff.fid=t.fid AND ff.type='forum'
LEFT JOIN cdb_forums sf ON sf.fid=t.fid AND sf.type='sub'
WHERE ((sf.fid='86' OR ff.fid='86') OR gf.fid='86')
ORDER BY t.lastpost DESC
LIMIT 0 , 24 ;表的设计:
mysql> desc cdb_threads;
+-----------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------------+------+-----+---------+----------------+
| tid | mediumint(8) unsigned | NO | PRI | NULL | auto_increment |
| fid | smallint(6) unsigned | NO | MUL | 0 | |
| iconid | smallint(6) unsigned | NO | | 0 | |
| typeid | smallint(6) unsigned | NO | | 0 | |
| sortid | smallint(6) unsigned | NO | MUL | 0 | |
| readperm | tinyint(3) unsigned | NO | | 0 | |
| price | smallint(6) | NO | | 0 | |
| author | char(15) | NO | | | |
| authorid | mediumint(8) unsigned | NO | MUL | 0 |
| subject | char(80) | NO | | | |
| dateline | int(10) unsigned | NO | | 0 | |
| lastpost | int(10) unsigned | NO | | 0 | |
| lastposter | char(15) | NO | | | |
| views | int(10) unsigned | NO | | 0 | |
| replies | mediumint(8) unsigned | NO | | 0 | |
| displayorder | tinyint(1) | NO | | 0 | |
| highlight | tinyint(1) | NO | | 0 | |
| digest | tinyint(1) | NO | MUL | 0 | |
| rate | tinyint(1) | NO | | 0 | |
| special | tinyint(1) | NO | | 0 | |
| attachment | tinyint(1) | NO | | 0 | |
| moderated | tinyint(1) | NO | | 0 | |
| closed | mediumint(8) unsigned | NO | | 0 | |
| itemid | mediumint(8) unsigned | NO | | 0 | |
| supe_pushstatus | tinyint(1) | NO | | 0 | |
| recommends | smallint(6) | NO | MUL | | |
| recommend_add | smallint(6) | NO | | | |
| recommend_sub | smallint(6) | NO | | | |
| heats | int(10) unsigned | NO | MUL | 0 | |
+-----------------+-----------------------+------+-----+---------+----------------+
29 rows in set (0.03 sec)
mysql> desc cdb_forums;
+------------------+-----------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-----------------------------+------+-----+---------+----------------+
| fid | smallint(6) unsigned | NO | PRI | NULL | auto_increment |
| fup | smallint(6) unsigned | NO | MUL | 0 | |
| type | enum('group','forum','sub') | NO | | forum | |
| name | char(50) | NO | | | |
| status | tinyint(1) | NO | MUL | 0 | |
| displayorder | smallint(6) | NO | | 0 | |
| styleid | smallint(6) unsigned | NO | | 0 | |
| threads | mediumint(8) unsigned | NO | | 0 | |
| posts | mediumint(8) unsigned | NO | | 0 | |
| todayposts | mediumint(8) unsigned | NO | | 0 | |
| lastpost | char(110) | NO | | | |
| allowsmilies | tinyint(1) | NO | | 0 | |
| allowhtml | tinyint(1) | NO | | 0 | |
| allowbbcode | tinyint(1) | NO | | 0 | |
| allowimgcode | tinyint(1) | NO | | 0 | |
| allowmediacode | tinyint(1) | NO | | 0 | |
| allowanonymous | tinyint(1) | NO | | 0 | |
| allowshare | tinyint(1) | NO | | 0 | |
| allowpostspecial | smallint(6) unsigned | NO | | 0 | |
| allowspecialonly | tinyint(1) unsigned | NO | | 0 | |
| alloweditrules | tinyint(1) | NO | | 0 | |
| allowfeed | tinyint(1) | NO | | 1 | |
| recyclebin | tinyint(1) | NO | | 0 | |
| modnewposts | tinyint(1) | NO | | 0 | |
| jammer | tinyint(1) | NO | | 0 | |
| disablewater | tinyint(1) | NO | | 0 | |
| inheritedmod | tinyint(1) | NO | | 0 | |
| autoclose | smallint(6) | NO | | 0 | |
| forumcolumns | tinyint(3) unsigned | NO | | 0 | |
| threadcaches | tinyint(1) | NO | | 0 | |
| alloweditpost | tinyint(1) unsigned | NO | | 1 | |
| simple | tinyint(1) unsigned | NO | | | |
| modworks | tinyint(1) unsigned | NO | | | |
| allowtag | tinyint(1) | NO | | 1 | |
| allowglobalstick | tinyint(1) | NO | | 1 | |
+------------------+-----------------------------+------+-----+---------+----------------+
35 rows in set (0.03 sec)forum中的类型有三种,(表forum中的字段type)
1.group -- 大论坛
2.forum -- 论坛
3.sub -- 子论坛。===============================================================================================
如何才可以选出,只已知一个fid,
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
数据库类型mysql。我用的SQL语句如下:
(
SELECT t . * , f . *
FROM cdb_threads t
LEFT JOIN cdb_forums f ON t.fid = f.fid
AND f.type = 'group'
LEFT JOIN cdb_forums ff ON ff.fup = f.fid
AND ff.type = 'forum'
LEFT JOIN cdb_forums sf ON sf.fup = ff.fid
AND sf.type = 'sub'
WHERE (
f.fid = '1'
OR (
ff.fid = '1'
OR sf.fid = '1'
)
)
)
UNION ALL (SELECT t . * , f . *
FROM cdb_threads t
LEFT JOIN cdb_forums f ON f.fid = t.fid
AND f.type = 'forum'
LEFT JOIN cdb_forums sf ON sf.fup = f.fid
AND sf.type = 'sub'
WHERE (
f.fid = '1'
OR sf.fid = '1'
)
)
UNION ALL (SELECT t . * , f . *
FROM cdb_threads t
LEFT JOIN cdb_forums f ON f.fid = t.fid
AND f.type = 'sub'
WHERE f.fid = '1'
)
ORDER BY tid DESC
LIMIT 0 , 24;--
-- 数据库: `discuz_full`
-- -- ----------------------------------------------------------
-- 表的结构 `cdb_forums`
-- CREATE TABLE `cdb_forums` (
`fid` smallint(6) unsigned NOT NULL auto_increment,
`fup` smallint(6) unsigned NOT NULL default '0',
`type` enum('group','forum','sub') NOT NULL default 'forum',
`name` char(50) NOT NULL default '',
`status` tinyint(1) NOT NULL default '0',
`displayorder` smallint(6) NOT NULL default '0',
`styleid` smallint(6) unsigned NOT NULL default '0',
`threads` mediumint(8) unsigned NOT NULL default '0',
`posts` mediumint(8) unsigned NOT NULL default '0',
`todayposts` mediumint(8) unsigned NOT NULL default '0',
`lastpost` char(110) NOT NULL default '',
`allowsmilies` tinyint(1) NOT NULL default '0',
`allowhtml` tinyint(1) NOT NULL default '0',
`allowbbcode` tinyint(1) NOT NULL default '0',
`allowimgcode` tinyint(1) NOT NULL default '0',
`allowmediacode` tinyint(1) NOT NULL default '0',
`allowanonymous` tinyint(1) NOT NULL default '0',
`allowshare` tinyint(1) NOT NULL default '0',
`allowpostspecial` smallint(6) unsigned NOT NULL default '0',
`allowspecialonly` tinyint(1) unsigned NOT NULL default '0',
`alloweditrules` tinyint(1) NOT NULL default '0',
`allowfeed` tinyint(1) NOT NULL default '1',
`recyclebin` tinyint(1) NOT NULL default '0',
`modnewposts` tinyint(1) NOT NULL default '0',
`jammer` tinyint(1) NOT NULL default '0',
`disablewater` tinyint(1) NOT NULL default '0',
`inheritedmod` tinyint(1) NOT NULL default '0',
`autoclose` smallint(6) NOT NULL default '0',
`forumcolumns` tinyint(3) unsigned NOT NULL default '0',
`threadcaches` tinyint(1) NOT NULL default '0',
`alloweditpost` tinyint(1) unsigned NOT NULL default '1',
`simple` tinyint(1) unsigned NOT NULL,
`modworks` tinyint(1) unsigned NOT NULL,
`allowtag` tinyint(1) NOT NULL default '1',
`allowglobalstick` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`fid`),
KEY `forum` (`status`,`type`,`displayorder`),
KEY `fup` (`fup`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=18 ;-- ----------------------------------------------------------
-- 表的结构 `cdb_threads`
-- CREATE TABLE `cdb_threads` (
`tid` mediumint(8) unsigned NOT NULL auto_increment,
`fid` smallint(6) unsigned NOT NULL default '0',
`iconid` 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',
`itemid` mediumint(8) unsigned NOT NULL default '0',
`supe_pushstatus` tinyint(1) NOT NULL default '0',
`recommends` smallint(6) NOT NULL,
`recommend_add` smallint(6) NOT NULL,
`recommend_sub` smallint(6) NOT NULL,
`heats` int(10) unsigned 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`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
--
-- 数据库: `discuz_full`
-- --
-- 导出表中的数据 `cdb_forums`
-- INSERT INTO `cdb_forums` VALUES (1, 0, 'group', '大论坛', 0, 0, 0, 0, 0, 0, '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 1, 1);
INSERT INTO `cdb_forums` VALUES (2, 0, 'forum', '二级论坛', 0, 0, 0, 0, 0, 0, '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 1, 1);
INSERT INTO `cdb_forums` VALUES (3, 0, 'sub', '三级论坛', 0, 0, 0, 0, 0, 0, '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 1, 1);--
-- 导出表中的数据 `cdb_threads`
-- INSERT INTO `cdb_threads` VALUES (11, 1, 0, 0, 0, 0, 0, '', 0, '直接放在大论坛下的帖子', 1261184536, 0, '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO `cdb_threads` VALUES (21, 2, 0, 0, 0, 0, 0, '', 0, '直接放在二级论坛下的帖子', 1261184553, 0, '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO `cdb_threads` VALUES (31, 3, 0, 0, 0, 0, 0, '', 0, '直接放在三级论坛下的帖子', 1261184576, 0, '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);mysql> select fid,type,name from cdb_forums;
+-----+-------+----------+
| fid | type | name |
+-----+-------+----------+
| 1 | group | 大论坛 |
| 2 | forum | 论坛 |
| 3 | sub | 下属论坛 |
+-----+-------+----------+
3 rows in set (0.02 sec)
mysql> select tid,fid,subject ,dateline from cdb_threads;
+-----+-----+--------------------------+------------+
| tid | fid | subject | dateline |
+-----+-----+--------------------------+------------+
| 11 | 1 | 直接放在大论坛下的帖子 | 1261184536 |
| 21 | 2 | 直接放在二级论坛下的帖子 | 1261184553 |
| 31 | 3 | 直接放在三级论坛下的帖子 | 1261184576 |
+-----+-----+--------------------------+------------+
3 rows in set (0.00 sec)
以上数据中:当fid为1的时候,显示 11,21,31这三个帖子,因为fid=1的论坛是大论坛,即type='group',(因为tid=11的fid=1,tid=21的论坛的上一级论坛的fid=2,tid=31的论坛的上一级论坛的上一级论坛的fid=1,当fid为2的时候,显示21,31这两个帖子,因为fid=2的时候的论坛是二级论坛,即type='forum',(因为tid=21的fid=2,tid=31的论坛的上一级论坛的fid=2)当fid为3的时候,只显示3这个帖子,因为fid=3的时候是三级论坛,即type='sub',(因为tid=31的论坛的fid=3)
不好意思,我现在简化下表:-- ----------------------------------------------------------
-- 表的结构 `cdb_forums`
-- CREATE TABLE `cdb_forums` (
`fid` smallint(6) unsigned NOT NULL auto_increment,
`fup` smallint(6) unsigned NOT NULL default '0',
`type` enum('group','forum','sub') NOT NULL default 'forum',
`name` char(50) NOT NULL default '',
PRIMARY KEY (`fid`),
KEY `forum` (`type`),
KEY `fup` (`fup`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;--
-- 导出表中的数据 `cdb_forums`
-- INSERT INTO `cdb_forums` VALUES (1, 0, 'group', '大论坛');
INSERT INTO `cdb_forums` VALUES (2, 0, 'forum', '论坛');
INSERT INTO `cdb_forums` VALUES (3, 0, 'sub', '下属论坛');--
-- 表的结构 `cdb_threads`
-- CREATE TABLE `cdb_threads` (
`tid` mediumint(8) unsigned NOT NULL auto_increment,
`fid` smallint(6) unsigned NOT NULL default '0',
`authorid` mediumint(8) unsigned NOT NULL default '0',
`subject` char(80) NOT NULL default '',
`dateline` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`tid`),
KEY `displayorder` (`fid`),
KEY `authorid` (`authorid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=32 ;--
-- 导出表中的数据 `cdb_threads`
-- INSERT INTO `cdb_threads` VALUES (11, 1, 0, '直接放在大论坛下的帖子', 1261184536);
INSERT INTO `cdb_threads` VALUES (21, 2, 0, '直接放在二级论坛下的帖子', 1261184553);
INSERT INTO `cdb_threads` VALUES (31, 3, 0, '直接放在三级论坛下的帖子', 1261184576);
+-----+-----+-------+----------+
| fid | fup | type | name |
+-----+-----+-------+----------+
| 1 | 0 | group | 大论坛 |
| 2 | 0 | forum | 论坛 |
| 3 | 0 | sub | 下属论坛 |
+-----+-----+-------+----------+
3 rows in set (0.00 sec)mysql> select * from cdb_threads;
+-----+-----+----------+--------------------------+------------+
| tid | fid | authorid | subject | dateline |
+-----+-----+----------+--------------------------+------------+
| 11 | 1 | 0 | 直接放在大论坛下的帖子 | 1261184536 |
| 21 | 2 | 0 | 直接放在二级论坛下的帖子 | 1261184553 |
| 31 | 3 | 0 | 直接放在三级论坛下的帖子 | 1261184576 |
+-----+-----+----------+--------------------------+------------+
3 rows in set (0.00 sec)mysql>期望的结果是什么?
SELECT t.*,f.*
FROM cdb_threads t
LEFT JOIN cdb_forums f ON t.fid=f.fid AND f.type='group'
LEFT JOIN cdb_forums ff ON ff.fup=f.fid AND ff.type='forum'
LEFT JOIN cdb_forums sf ON sf.fup=ff.fid AND sf.type='sub'
WHERE f.fid=2
)
UNION ALL
(
SELECT t.*,f.*
FROM cdb_threads t
LEFT JOIN cdb_forums f ON t.fid=f.fid AND f.type='forum'
LEFT JOIN cdb_forums sf ON sf.fup=f.fid AND sf.type='sub'
WHERE f.fid=2
)
UNION ALL
(
SELECT t.*,f.*
FROM cdb_threads t
LEFT JOIN cdb_forums f ON t.fid=f.fid AND f.type='sub'
WHERE f.fid=2
)
ORDER BY tid DESC
LIMIT 0,24;
用了新的语句还是不行。
| tid | fid | authorid | subject | dateline | fid | fup | type | name |
+-----+-----+----------+--------------------------+------------+------+------+-------+----------+
| 11 | 1 | 0 | 直接放在大论坛下的帖子 | 1261184536 | 1 | 0 | group | 大论坛 |
| 21 | 2 | 0 | 直接放在二级论坛下的帖子 | 1261184553 | 2 | 0 | forum | 论坛 |
| 31 | 3 | 0 | 直接放在三级论坛下的帖子 | 1261184576 | 3 | 0 | sub | 下属论坛 |
+-----+-----+----------+--------------------------+------------+------+------+-------+----------+
3 rows in set (0.00 sec)当fid=2的时候+-----+-----+----------+--------------------------+------------+------+------+-------+----------+
| tid | fid | authorid | subject | dateline | fid | fup | type | name |
+-----+-----+----------+--------------------------+------------+------+------+-------+----------+
| 21 | 2 | 0 | 直接放在二级论坛下的帖子 | 1261184553 | 2 | 0 | forum | 论坛 |
| 31 | 3 | 0 | 直接放在三级论坛下的帖子 | 1261184576 | 3 | 0 | sub | 下属论坛 |
+-----+-----+----------+--------------------------+------------+------+------+-------+----------+
3 rows in set (0.00 sec)
当fid=3的时候+-----+-----+----------+--------------------------+------------+------+------+-------+----------+
| tid | fid | authorid | subject | dateline | fid | fup | type | name |
+-----+-----+----------+--------------------------+------------+------+------+-------+----------+
| 31 | 3 | 0 | 直接放在三级论坛下的帖子 | 1261184576 | 3 | 0 | sub | 下属论坛 |
+-----+-----+----------+--------------------------+------------+------+------+-------+----------+
3 rows in set (0.00 sec)
是这个数据:
fid=2的fup=1不是=0
fid=3的fup=2不是等于0mysql> select * from cdb_forums;
+-----+-----+-------+----------+
| fid | fup | type | name |
+-----+-----+-------+----------+
| 1 | 0 | group | 大论坛 |
| 2 | 1 | forum | 论坛 |
| 3 | 2 | sub | 下属论坛 |
+-----+-----+-------+----------+
3 rows in set (0.00 sec)
| fid | fup | type | name |
+-----+-----+-------+----------+
| 1 | 0 | group | 大论坛 |
| 2 | 0 | forum | 论坛 |
| 3 | 0 | sub | 下属论坛 |
+-----+-----+-------+----------+相互隶属关系在哪里体现?
+-----+-----+-------+----------+
| fid | fup | type | name |
+-----+-----+-------+----------+
| 1 | 0 | group | 大论坛 |
| 2 | 1 | forum | 论坛 |
| 3 | 2 | sub | 下属论坛 |
+-----+-----+-------+----------+
3 rows in set (0.00 sec)
恩,是的,最多三层,forum字段type 类型 | type | enum('group','forum','sub') | NO | | forum | |
Query OK, 0 rows affected (0.00 sec)mysql> select *
-> from cdb_threads
-> where fid=@x
-> or fid in (select fid from cdb_forums where fup=@x)
-> or fid in (select t2.fid from cdb_forums t1 inner join cdb_forums t2 on t1.fid=t2.fup where t1.fup=@x);
+-----+-----+----------+--------------------------+------------+
| tid | fid | authorid | subject | dateline |
+-----+-----+----------+--------------------------+------------+
| 11 | 1 | 0 | 直接放在大论坛下的帖子 | 1261184536 |
| 21 | 2 | 0 | 直接放在二级论坛下的帖子 | 1261184553 |
| 31 | 3 | 0 | 直接放在三级论坛下的帖子 | 1261184576 |
+-----+-----+----------+--------------------------+------------+
3 rows in set (0.00 sec)mysql> set @x=2;
Query OK, 0 rows affected (0.00 sec)mysql> select *
-> from cdb_threads
-> where fid=@x
-> or fid in (select fid from cdb_forums where fup=@x)
-> or fid in (select t2.fid from cdb_forums t1 inner join cdb_forums t2 on t1.fid=t2.fup where t1.fup=@x);
+-----+-----+----------+--------------------------+------------+
| tid | fid | authorid | subject | dateline |
+-----+-----+----------+--------------------------+------------+
| 21 | 2 | 0 | 直接放在二级论坛下的帖子 | 1261184553 |
| 31 | 3 | 0 | 直接放在三级论坛下的帖子 | 1261184576 |
+-----+-----+----------+--------------------------+------------+
2 rows in set (0.00 sec)mysql> set @x=3;
Query OK, 0 rows affected (0.00 sec)mysql> select *
-> from cdb_threads
-> where fid=@x
-> or fid in (select fid from cdb_forums where fup=@x)
-> or fid in (select t2.fid from cdb_forums t1 inner join cdb_forums t2 on t1.fid=t2.fup where t1.fup=@x);
+-----+-----+----------+--------------------------+------------+
| tid | fid | authorid | subject | dateline |
+-----+-----+----------+--------------------------+------------+
| 31 | 3 | 0 | 直接放在三级论坛下的帖子 | 1261184576 |
+-----+-----+----------+--------------------------+------------+
1 row in set (0.00 sec)mysql>
Query OK, 0 rows affected (0.00 sec)mysql> select @x:=concat(@x,',',group_concat(fid))
-> from cdb_forums
-> where find_in_set(fup,@x);
+--------------------------------------+
| @x:=concat(@x,',',group_concat(fid)) |
+--------------------------------------+
| 1,2 |
+--------------------------------------+
1 row in set (0.00 sec)mysql> select @x:=concat(@x,',',group_concat(fid))
-> from cdb_forums
-> where find_in_set(fup,@x);
+--------------------------------------+
| @x:=concat(@x,',',group_concat(fid)) |
+--------------------------------------+
| 1,2,2,3 |
+--------------------------------------+
1 row in set (0.00 sec)mysql>
mysql> select * from cdb_threads
-> where find_in_set(fid,@x)
-> order by dateline desc;
+-----+-----+----------+--------------------------+------------+
| tid | fid | authorid | subject | dateline |
+-----+-----+----------+--------------------------+------------+
| 31 | 3 | 0 | 直接放在三级论坛下的帖子 | 1261184576 |
| 21 | 2 | 0 | 直接放在二级论坛下的帖子 | 1261184553 |
| 11 | 1 | 0 | 直接放在大论坛下的帖子 | 1261184536 |
+-----+-----+----------+--------------------------+------------+
3 rows in set (0.00 sec)mysql>
mysql> set @x='2';
Query OK, 0 rows affected (0.00 sec)mysql> select @x:=concat(@x,',',group_concat(fid))
-> from cdb_forums
-> where find_in_set(fup,@x);
+--------------------------------------+
| @x:=concat(@x,',',group_concat(fid)) |
+--------------------------------------+
| 2,3 |
+--------------------------------------+
1 row in set (0.02 sec)mysql> select @x:=concat(@x,',',group_concat(fid))
-> from cdb_forums
-> where find_in_set(fup,@x);
+--------------------------------------+
| @x:=concat(@x,',',group_concat(fid)) |
+--------------------------------------+
| 2,3,3 |
+--------------------------------------+
1 row in set (0.00 sec)mysql>
mysql> select * from cdb_threads
-> where find_in_set(fid,@x)
-> order by dateline desc;
+-----+-----+----------+--------------------------+------------+
| tid | fid | authorid | subject | dateline |
+-----+-----+----------+--------------------------+------------+
| 31 | 3 | 0 | 直接放在三级论坛下的帖子 | 1261184576 |
| 21 | 2 | 0 | 直接放在二级论坛下的帖子 | 1261184553 |
+-----+-----+----------+--------------------------+------------+
2 rows in set (0.00 sec)mysql>
我终于试出了一个了,测试暂时没有问题的。
SELECT t. * , f. *
FROM cdb_threads t
LEFT JOIN cdb_forums f ON f.fid = t.fid
LEFT JOIN cdb_forums gf ON gf.fid = f.fup
LEFT JOIN cdb_forums sf ON sf.fup = f.fid
WHERE f.fup =3
OR f.fid =3
LIMIT 0 , 30