34,593
社区成员
发帖
与我相关
我的任务
分享
select r.rtitle as 帖子标题,count(*) as 总回复数,
CONVERT(varchar(100), r.rTime, 23)as 发帖时间 from bbsTz r
left join bbsFt p on p.TzId=r.id
GROUP BY rtitle,rTime
order by r.rTime desc
/*结果
帖子标题 总回复数 发帖时间
The Third 10 2012-02-05
The Second 3 2012-02-03
The Frist 2 2012-02-01
*/
/*
--结果刚才没复制过来
ID 标题 回帖人数 最新回帖时间
----------- -------- ----------- -----------------------
12 ddddd 0 2011-08-14 06:21:45.000
1 aaaaa 4 2011-07-14 06:21:45.000
2 bbbbb 2 2011-07-12 06:21:45.000
5 ccccc 2 2011-07-11 06:21:45.000
(4 行受影响)
[Quote=引用 12 楼 geniuswjt 的回复:]
--加了个ID=12的未回帖的例子,LZ自己看自己改成access的
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (ID int,回帖ID int,标题 varchar(8),发帖时间 datetime)
insert into [tb]
select 1,0,'aaaaa','2011-7-4 05:30:41' union all
select 2,0,'bbbbb','2011-7-5 06:21:45' union all
select 3,1,'Re:aaaaa','2011-7-6 06:21:45' union all
select 4,2,'Re:bbbbb','2011-7-7 06:21:45' union all
select 5,0,'ccccc','2011-7-8 06:21:45' union all
select 6,1,'Re:aaaaa','2011-7-9 06:21:45' union all
select 7,5,'Re:ccccc','2011-7-10 06:21:45' union all
select 8,5,'Re:ccccc','2011-7-11 06:21:45' union all
select 9,2,'Re:bbbbb','2011-7-12 06:21:45' union all
select 10,1,'Re:aaaaa','2011-7-13 06:21:45' union all
select 11,1,'Re:aaaaa','2011-7-14 06:21:45'
--开始查询
select a.ID,a.标题,isnull(b.回帖人数,0) 回帖人数,isnull(b.回帖时间,a.发帖时间) 最新回帖时间 from (
select * from [tb] where 回帖ID=0
) a left join (
select 回帖ID,max(发帖时间) 回帖时间,count(1) 回帖人数 from [tb] where 回帖ID<>0 group by 回帖ID
) b on (a.id=b.回帖ID)
order by 4 desc
--结束查询
drop table [tb]
/*
select a.ID,a.标题,isnull(b.回帖人数,0) 回帖人数,isnull(b.回帖时间,a.发帖时间) 最新回帖时间 from (
select * from [tb] where 回帖ID=0
) a left join (
select 回帖ID,max(发帖时间) 回帖时间,count(1) 回帖人数 from [tb] where 回帖ID<>0 group by 回帖ID
) b on (a.id=b.回帖ID)
order by 4 desc
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (ID int,回帖ID int,标题 varchar(8),发帖时间 datetime)
insert into [tb]
select 1,0,'aaaaa','2011-7-4 05:30:41' union all
select 2,0,'bbbbb','2011-7-5 06:21:45' union all
select 3,1,'Re:aaaaa','2011-7-6 06:21:45' union all
select 4,2,'Re:bbbbb','2011-7-7 06:21:45' union all
select 5,0,'ccccc','2011-7-8 06:21:45' union all
select 6,1,'Re:aaaaa','2011-7-9 06:21:45' union all
select 7,5,'Re:ccccc','2011-7-10 06:21:45' union all
select 8,5,'Re:ccccc','2011-7-11 06:21:45' union all
select 9,2,'Re:bbbbb','2011-7-12 06:21:45' union all
select 10,1,'Re:aaaaa','2011-7-13 06:21:45' union all
select 11,1,'Re:aaaaa','2011-7-14 06:21:45'
--开始查询
select a.ID,a.标题,b.num 回帖人数,a.发帖时间 from (
select * from [tb] where 回帖ID=0
) a join (
select 回帖ID,count(1) num from [tb] where 回帖ID<>0 group by 回帖ID
) b on (a.id=b.回帖ID)
order by 4 desc
--结束查询
drop table [tb]
/*
ID 标题 回帖人数 发帖时间
----------- -------- ----------- -----------------------
5 ccccc 2 2011-07-08 06:21:45.000
2 bbbbb 2 2011-07-05 06:21:45.000
1 aaaaa 4 2011-07-04 05:30:41.000
(3 行受影响)
[Quote=引用 5 楼 asdfx110 的回复:]if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb ([ID] int,[回帖ID] int,[标题] nvarchar(16),[发帖时间] datetime)
insert into #tb
select 1,0,'aaaaa','2011-7-4 05:30:41' union all
select 2,0,'bbbbb','2011-7-5 06:21:45' union all
select 3,1,'Re:aaaaa','2011-7-6 06:21:45' union all
select 4,2,'Re:bbbbb','2011-7-7 06:21:45' union all
select 5,0,'ccccc','2011-7-8 06:21:45' union all
select 6,1,'Re:aaaaa','2011-7-9 06:21:45' union all
select 7,5,'Re:ccccc','2011-7-10 06:21:45' union all
select 8,5,'Re:ccccc','2011-7-11 06:21:45' union all
select 9,2,'Re:bbbbb','2011-7-12 06:21:45' union all
select 10,1,'Re:aaaaa','2011-7-13 06:21:45' union all
select 11,1,'Re:aaaaa','2011-7-14 06:21:45'
select a.ID,
a.[标题],
b.回帖人数,
a.[发帖时间]
from #tb a
left join
(select count(*)回帖人数,
[标题]
from #tb
where [标题] like 'Re:%'
group by [标题]
)b
on 'Re:'+a.[标题]=b.[标题]
where a.[标题] not like 'Re:%'
/*
ID 标题 回帖人数 发帖时间
----------- ---------------- ----------- -----------------------
1 aaaaa 4 2011-07-04 05:30:41.000
2 bbbbb 2 2011-07-05 06:21:45.000
5 ccccc 2 2011-07-08 06:21:45.000
(3 row(s) affected)
*/