select bbs.BbsTitle, u.UsersName,'add'=isnull(br.AddTime,bbs.AddTime),'ent'=isnull(e.cnt,0), bbs.ReadTimes,'us'=isnull(br.UsersID,bbs.UsersID) from Bbs bbs left join BbsRep br on bbs.BbsID=br.BbsID left join Users u on bbs.UsersID=u.UsersID left join (select BbsID,'cnt'=count(BbsID) from BbsRep group by BbsID)e on e.BbsID=bbs.BbsID where bbs.ClubBBSID='2' order by br.AddTime desc 我写了一句,结果不对! 显示成了回复的条数,其实主题数只有2条,一共回复了3条,这个结果就成了三条! 不好意思,小弟,菜,不知道怎么弄!! 请高手帮看一下
--查看数不知道怎么算? select a.BbsTitle 标题 , b.UsersName 发帖人 , a.AddTime 发帖时间 , c.回复总数 , d.UsersName 最后回复人 from Bbs a, Users b , (select BbsID , count(*) 回复总数 from BbsRep group by BbsID) c, (select m.BbsID , n.UsersName from Users m,(select BbsID , UsersID from BbsRep t where AddTime = (select max(AddTime) from BbsRep where BbsID = t.BbsID)) n where m.UsersID = n.UsersID) d where a.UsersID = b.UsersID and a.BbsID = c.BbsID and a.BbsID = d.BbsID
--点击数就是查看数?select a.BbsTitle 标题 , b.UsersName 发帖人 , a.AddTime 发帖时间 , c.回复总数 , a.ReadTimes 查看数 , d.UsersName 最后回复人 from Bbs a, Users b , (select BbsID , count(*) 回复总数 from BbsRep group by BbsID) c, (select m.BbsID , n.UsersName from Users m,(select BbsID , UsersID from BbsRep t where AddTime = (select max(AddTime) from BbsRep where BbsID = t.BbsID)) n where m.UsersID = n.UsersID) d where a.UsersID = b.UsersID and a.BbsID = c.BbsID and a.BbsID = d.BbsID
楼上的不对!!!select a.BbsTitle , b.UsersName, a.AddTime, c.cnt , a.ReadTimes, d.UsersName from Bbs a, Users b , (select BbsID , 'cnt'=count(*) from BbsRep group by BbsID) c, (select m.BbsID , n.UsersName from Users m,(select BbsID , UsersID from BbsRep t where AddTime = (select max(AddTime) from BbsRep where BbsID = t.BbsID)) n where m.UsersID = n.UsersID) d where a.UsersID = b.UsersID and a.BbsID = c.BbsID and a.BbsID = d.BbsID服务器: 消息 207,级别 16,状态 3,行 1 列名 'BbsID' 无效。 服务器: 消息 207,级别 16,状态 1,行 1 列名 'UsersName' 无效。
select bbs.BbsTitle, u.UsersName,'add'=isnull(br.AddTime,bbs.AddTime),'ent'=isnull(e.cnt,0), bbs.ReadTimes,'us'=isnull(br.UsersID,bbs.UsersID) from Bbs bbs left join BbsRep br on bbs.BbsID=br.BbsID left join Users u on bbs.UsersID=u.UsersID left join (select BbsID,'cnt'=count(BbsID) from BbsRep group by BbsID)e on e.BbsID=bbs.BbsID where bbs.ClubBBSID='传入值' order by br.AddTime desc 这个是我调试的SQL,就是显示不正确!!高手帮小弟看一下
select bbs.BbsTitle, u.UsersName,'add'=isnull(br.AddTime,bbs.AddTime),'ent'=isnull(e.cnt,0), bbs.ReadTimes,'us'=isnull(br.UsersID,bbs.UsersID)
from Bbs bbs
left join BbsRep br on bbs.BbsID=br.BbsID
left join Users u on bbs.UsersID=u.UsersID
left join (select BbsID,'cnt'=count(BbsID) from BbsRep group by BbsID)e on e.BbsID=bbs.BbsID where bbs.ClubBBSID='2'
order by br.AddTime desc 我写了一句,结果不对!
显示成了回复的条数,其实主题数只有2条,一共回复了3条,这个结果就成了三条!
不好意思,小弟,菜,不知道怎么弄!!
请高手帮看一下
--查看数不知道怎么算?
select a.BbsTitle 标题 , b.UsersName 发帖人 , a.AddTime 发帖时间 , c.回复总数 , d.UsersName 最后回复人 from
Bbs a, Users b ,
(select BbsID , count(*) 回复总数 from BbsRep group by BbsID) c,
(select m.BbsID , n.UsersName from Users m,(select BbsID , UsersID from BbsRep t where AddTime = (select max(AddTime) from BbsRep where BbsID = t.BbsID)) n where m.UsersID = n.UsersID) d
where a.UsersID = b.UsersID and a.BbsID = c.BbsID and a.BbsID = d.BbsID
Bbs a, Users b ,
(select BbsID , count(*) 回复总数 from BbsRep group by BbsID) c,
(select m.BbsID , n.UsersName from Users m,(select BbsID , UsersID from BbsRep t where AddTime = (select max(AddTime) from BbsRep where BbsID = t.BbsID)) n where m.UsersID = n.UsersID) d
where a.UsersID = b.UsersID and a.BbsID = c.BbsID and a.BbsID = d.BbsID
Bbs a, Users b ,
(select BbsID , 'cnt'=count(*) from BbsRep group by BbsID) c,
(select m.BbsID , n.UsersName from Users m,(select BbsID , UsersID from BbsRep t where AddTime = (select max(AddTime) from BbsRep where BbsID = t.BbsID)) n where m.UsersID = n.UsersID) d
where a.UsersID = b.UsersID and a.BbsID = c.BbsID and a.BbsID = d.BbsID服务器: 消息 207,级别 16,状态 3,行 1
列名 'BbsID' 无效。
服务器: 消息 207,级别 16,状态 1,行 1
列名 'UsersName' 无效。
Users.UsersID=Bbs.UsersID
Users.UsersID=BbsRep.UsersID
Bbs.BbsID=BbsRep.BbsID 就是这样的关系啊!!!!
你看一下
from Bbs bbs
left join BbsRep br on bbs.BbsID=br.BbsID
left join Users u on bbs.UsersID=u.UsersID
left join (select BbsID,'cnt'=count(BbsID) from BbsRep group by BbsID)e on e.BbsID=bbs.BbsID where bbs.ClubBBSID='传入值'
order by br.AddTime desc 这个是我调试的SQL,就是显示不正确!!高手帮小弟看一下
---------------------------------------------------------------
建议把你要在列表显示的数据都放到一bbs表里,否则,访问量比较大的时候,几乎没有人可以访问你的bbs了
UsersID UsersName
1 frankres
2 xiaoxiao表:Bbs
BbsID BbsTitle ClubBBSID UsersID AddTime ReadTimes
101 We are young! 2 2 2008-8-5 20
102 I love you! 2 1 2008-8-6 14表:BbsRep
BbsID BbsRepContent AddTime UsersID
101 yes! 2008-8-6 2
101 Good! 2008-8-7 1
102 Bad! 2008-8-8 2我想要的结果就是:
标题 发帖人 发帖时间 回复总数 查看数 最后回复人
We are young! xiaoxiao 2008-8-5 2 20 frankres
I love you! frankres 2008-8-6 1 14 xiaoxiao
UsersID UsersName
1 frankres
2 xiaoxiao 表:Bbs
BbsID BbsTitle ClubBBSID UsersID AddTime ReadTimes
101 We are young! 2 2 2008-8-5 20
102 I love you! 2 1 2008-8-6 14
103 I want you! 2 1 2008-8-8 8表:BbsRep
BbsID BbsRepContent AddTime UsersID
101 yes! 2008-8-6 2
101 Good! 2008-8-7 1
102 Bad! 2008-8-8 2 我想要的结果就是:
标题 发帖人 发帖时间 回复总数 查看数 最后回复人
We are young! xiaoxiao 2008-8-5 2 20 frankres
I love you! frankres 2008-8-6 1 14 xiaoxiao
I want you! xiaoxiao 2008-8-8 0 8 xiaoxiao --没有回复则为发帖人的名字我改了一下Sql语句:
select a.BbsTitle , b.UsersName, a.AddTime, c.cnt , a.ReadTimes, d.UsersName from
Bbs a,
Users b,
(select BbsID , 'cnt'=count(*) from BbsRep group by BbsID) c,
(select n.BbsID , m.UsersName from Users m,
(select BbsID , UsersID from BbsRep t where AddTime = (select max(AddTime) from BbsRep where BbsID = t.BbsID)) n
where m.UsersID = n.UsersID) d
where a.UsersID = b.UsersID and a.BbsID = c.BbsID and a.BbsID = d.BbsID and a.ClubBBSID='2'结果却是这样:
标题 发帖人 发帖时间 回复总数 查看数 最后回复人
We are young! xiaoxiao 2008-8-5 2 20 frankres
I love you! frankres 2008-8-6 1 14 xiaoxiao 帮看一下!
http://topic.csdn.net/u/20080911/09/2b9f679a-47b4-4d36-9979-e4b23b299321.html