22,301
社区成员




create table news(
id int identity(1,1) primary key,
title varchar(500) not null,
content varchar(4000) null,
createtime datetime not null,
caid char(10) null)
create table comment(
id int identity(1,1) primary key,
content varchar(4000) null,
createtime datetime null,
userip char(20) null,
newsid int not null)
insert news
select '1','a','20090506','1'
union all
select '2','b','20090507','2'
insert comment
select 'good1','20090507','192..',1
union all
select 'good2','20090606','192..',1
union all
select 'bad1','20090607','192..',1
union all
select 'bad2','20090608','192..',1
union all
select 'good11','20090509','192..',2
union all
select 'good22','20090620','192..',2
union all
select 'bad11','20090621','192..',2
union all
select 'bad22','20090625','192..',2
WITH ATC AS(
SELECT content,createtime,newsid
FROM comment T
WHERE NOT EXISTS(SELECT 1 FROM comment WHERE newsid=T.newsid
AND [createtime]>T.[createtime]))
SELECT distinct NEWS.title ,ATC.*
FROM ATC,NEWS
WHERE NEWS.caid = ATC.newsid