跪求一条sql语句,大家来帮忙
表结构大概是这样的:
话题回复表   表名:GroupTopic
字段TopicID   int   自动增长
        ParentTopicID     父级话题ID   可以为空
        Title 标题
        Content 内容
        CreateTime 创建时间
        VisitCount 访问次数
        TopicURL 话题URL

需要得到的记录为话题TopicID     Title   TopicURL   回复次数     最后访问时间
(说明:最后访问时间是回复话题的创建时间,若没有回复,则回复时间为该话题的创建时间)

我写的sql语句只能取得TopicID     Title   TopicURL   回复次数   ,但不知道如何取得最后访问时间,希望各位善人给个解

select   c.TopicID,c.Title,c.Content,c.TopicURL,isnull(d.sl,0)   as   sl   from
(select   a.TopicID,a.Title,a.Content,a.TopicURL   from  
GroupTopic   a   left   join   GroupTopic   b   on   a.TopicID   =   b.ParentTopicID
where   (a.ParentTopicID   IS   NULL)
group   by   a.TopicID,a.Title,a.Content,a.TopicURL)c
left   join  
(select   a.TopicID,count(*)   as   sl
from   GroupTopic   a   inner   join   GroupTopic   b   on   a.TopicID   =   b.ParentTopicID
group   by   a.TopicID)   d
on   c.TopicID   =   d.TopicID

------解决方案--------------------
declare @table table
(
topicId int identity(1, 1),
parentTopicId int,
title varchar(50),
content varchar(200),
createTime datetime,
visitCount int,
topicUrl varchar(50)
)

insert into @table (parentTopicId, title, content, createtime, visitCount, topicUrl) values (null, '主题 ', '12341324 ', '2007-1-1 20:00:00 ', 2, 'xxx.aspx ');
insert into @table (parentTopicId, title, content, createtime, visitCount, topicUrl) values (1, '回复1 ', 'asdfasdf ', '2007-1-1 22:23:33 ', 0, null);
insert into @table (parentTopicId, title, content, createtime, visitCount, topicUrl) values (1, '回复2 ', 'zxcvzxcv ', '2007-1-1 23:11:23 ', 0, null);

select topicId, title, topicUrl,
(select isnull(count(*), 0) from @table where parentTopicId = a.topicId) as cnt,
(select top 1 createTime from @table where parentTopicId = a.topicId order by createTime desc) as lastTime
from @table a where parentTopicId is null
------解决方案--------------------
不就是一个表吗?? select TopicID,Title,TopicURL,CreateTime,case when visitcount is null then createtime else lasttime end from GroupTopic
---------------------------
lasttime是我建的列!记录最后访问时间!可能是你忘记写了!
------解决方案--------------------
try

Select
A.TopicID,
A.Title,
A.Content,
A.TopicURL,
IsNull(B.sl, 0) As sl,
IsNull(B.CreateTime, A.CreateTime) As lasttime
From
GroupTopic A
Left Join
(Select
ParentTopicID,
Count(*) As sl,
Max(CreateTime) As CreateTime
From
GroupTopic
Group By
ParentTopicID) B
On A.TopicID = B.ParentTopicID
------解决方案--------------------
主题和回复放在一个表里很影响查询效率