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)
go
insert into news(title,content,createtime,caid) select '1','a',getdate(),'1' union all select '2','b',getdate(),'2'
go
insert into comment(content,createtime,userip,newsid) values('good',getdate(),'192..',1)
go
insert into comment(content,createtime,userip,newsid) values('greate',getdate(),'192..',1)
go
insert into comment(content,createtime,userip,newsid) values('bad',getdate(),'192..',1)select a.* from news a,
(select b.* from comment b where not exists(select * from comment c where b.createtime<c.createtime)) d
where a.id*=d.newsid
最新一次的
(select b.* from comment b where not exists(select * from comment c where b.createtime<c.createtime)) d
where a.id*=d.newsid
上面少了红色部分
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