34,576
社区成员
发帖
与我相关
我的任务
分享求助三表查询???
我建了3个表,用户表bbsusers,主贴表bbstopic,跟贴表bbsreply.
现在我要根据一篇帖子固定(主贴id)TID=5查询该贴主贴表和跟贴表信息,我在查询主贴作者和跟贴作者时,无法分别哪个是主贴和跟贴作者.
SELECT T.TTopic AS 主贴标题, U.UName AS 作者姓名,T.TContents AS 主贴内容,
R.RTopic AS 跟贴标题, R.RContents AS 跟贴内容,R.RTime AS 跟贴时间
FROM BBSUsers AS U
INNER JOIN BBSTopic AS T ON (U.UID=T.TUID)
INNER JOIN BBSReply AS R ON (T.TUID=R.RUID)
WHERE T.TID=5
IN(SELECT U.UName AS 跟贴人姓名 FROM BBSUsers AS U INNER JOIN BBSReply AS R ON (U.UID=R.RUID))
现在我要根据一篇帖子固定(主贴id)TID=5查询该贴主贴表和跟贴表信息,我在查询主贴作者和跟贴作者时,无法分别哪个是主贴和跟贴作者.
SELECT T.TTopic AS 主贴标题, U.UName AS 作者姓名,T.TContents AS 主贴内容,
R.RTopic AS 跟贴标题, R.RContents AS 跟贴内容,R.RTime AS 跟贴时间
FROM BBSUsers AS U
INNER JOIN BBSTopic AS T ON (U.UID=T.TUID)
INNER JOIN BBSReply AS R ON (T.TUID=R.RUID)
WHERE T.TID=5
IN(SELECT U.UName AS 跟贴人姓名 FROM BBSUsers AS U INNER JOIN BBSReply AS R ON (U.UID=R.RUID))
...全文
请发表友善的回复…
发表回复
hellowork 2006-08-02
- 打赏
- 举报
假设跟帖表BBSReply与主贴表BBSTopic的TID关联的列名称为RTID,则这样:
SELECT t.TTopic as 主贴标题,u.UName as 主贴作者,t.TContens as 主贴内容,
r.RTopic as 跟帖标题,r.RContents as 跟帖内容,r.RTime as 跟帖时间, v.UName as 跟贴作者
FROM BBSTopic as t
inner join BBSReply as r on r.rtid = t.tid
left join BBSUsers as u on u.uid = t.uid
left join BBSUsers as v on v.uid = r.ruid
WHERE t.tid = 5
SELECT t.TTopic as 主贴标题,u.UName as 主贴作者,t.TContens as 主贴内容,
r.RTopic as 跟帖标题,r.RContents as 跟帖内容,r.RTime as 跟帖时间, v.UName as 跟贴作者
FROM BBSTopic as t
inner join BBSReply as r on r.rtid = t.tid
left join BBSUsers as u on u.uid = t.uid
left join BBSUsers as v on v.uid = r.ruid
WHERE t.tid = 5
