string strsql = "select post.postid,post.post_title,post.name,post.class,post.time,reply.name,count(replyid) as aaaa from post LEFT outer JOIN reply ON( post.postid=reply.postid) group by post.postid,post.post_title,post.name,post.class,post.time"各位大虾,这条语句目的是要查询帖子标题,帖子的id,帖子的回复数量,现在我还想查询最后回复人的姓名怎么做啊
表 post
列名postid post_title post name time class
表 reply
列名replyid reply postid time name
表 post
列名postid post_title post name time class
表 reply
列名replyid reply postid time name
select *, (select top 1 name from reply where a.postid=postid order by Time desc) as names from post a
select a.postid,a.post_title,a.name,a.class,a.time,(select count(*) from reply where a.postid=postid) as 回复总数 ,(select top 1 name from reply where a.postid=postid order by Time desc) as 最后回复人姓名 from post a
from post LEFT outer JOIN reply ON( post.postid=reply.postid)
group by post.postid,post.post_title,post.name,post.class,post.time
order by post.time desc
select count(*) as 帖子数量 from 帖子
select 姓名 from 帖子 order by 时间 desc
这么读帖子列表的时候,就简单了。order 回复时间 desc就可以了。这样性能也可以提高,因为帖子列表看的人多,回复的人少。有问题请到我的博客留言,我会及时回复的。