做的一个类似论坛表,把发帖和回帖放在一张表中,结构如下ID 回帖ID 标题 发帖时间
还有其他项就不列了,id是1开始的自增字段,如果是主题帖那个回帖的ID是0现在要查询 标题,回帖人数和最后发帖时间,并按最后发帖时间降序排序,最后发帖时间是同一个回帖ID 最后回复的那个
还有其他项就不列了,id是1开始的自增字段,如果是主题帖那个回帖的ID是0现在要查询 标题,回帖人数和最后发帖时间,并按最后发帖时间降序排序,最后发帖时间是同一个回帖ID 最后回复的那个
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
*/
1 0 aaaaa 2011-7-4 05:30:41
2 0 bbbbb 2011-7-5 06:21:45
3 1 Re:aaaaa 2011-7-6 06:21:45
4 2 Re:bbbbb 2011-7-7 06:21:45
5 0 ccccc 2011-7-8 06:21:45
6 1 Re:aaaaa 2011-7-9 06:21:45
7 5 Re:ccccc 2011-7-10 06:21:45
8 5 Re:ccccc 2011-7-11 06:21:45
9 2 Re:bbbbb 2011-7-12 06:21:45
10 1 Re:aaaaa 2011-7-13 06:21:45
11 1 Re:aaaaa 2011-7-14 06:21:45
查询的结果为:
ID 标题 回帖人数 发帖时间
5 ccccc 2 2011-7-8 06:21:45
2 bbbbb 2 2011-7-5 06:21:45
1 aaaaa 4 2011-7-4 05:30:41
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)
*/
--> 测试数据: [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 行受影响)
与回帖ID
用7#的吧
1 aaaaa 4 2011-7-4 05:30:41
2 bbbbb 2 2011-7-5 06:21:45
5 ccccc 2 2011-7-8 06:21:45
排列是按照每组中的回帖时间的降序排列
如果没回帖的就是发帖时间
--加了个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
/*
--结果刚才没复制过来
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 行受影响)