select a.*, isnull(max(b.PubLisDate), a.PublisDate) as LastPublisDate, count(b.PubThreadID) as ThreadCount
from dbo.PubMainThread a left join dbo.PubThread b
on a.PubMainThreadID = b.PubMainThreadID
where PubCategoryID = 1
group by a.PubMainThreadID, a.PubCategoryID,
a.UserID, a.PublisDate, a.ThreadTitle, a.ThreadContent, a.ReadCount
declare @主帖 table(id int identity(1,1),name varchar(100))
declare @回帖 table(id int identity(1,1),pid int,name varchar(100),replytime datetime)
insert @主帖(name)
select '第1封帖' union all
select '第2封帖'
insert @回帖(pid,name,replytime)
select '1','第1封帖的第1回复','2007-7-1 11:00' union all
select '1','第1封帖的第2回复','2007-7-1 12:00' union all
select '2','第2封帖的第1回复','2007-7-1 11:00' union all
select '2','第2封帖的第2回复','2007-7-1 14:00' union all
select '2','第2封帖的第3回复','2007-7-1 15:00'
select * from @主帖
select * from @回帖
select
a.name,
count(*),
max(replytime)
from @主帖 a
inner join @回帖 b
on a.id = b.pid
group by a.name