求两张关联表唯一一条数据

[zhougonghenbang]

假设:

以上是一对多关系,需要使用连接查询,一个新闻有n个评论,使用连接查询如何取出所有新闻记录,并且只取评论表相对应的一条评论记录,而不是所有新闻和所有评论????请各位大大指教。

[wdwen1986]

要最新一次的评论，还是第一次的评论啊？

[zhougonghenbang]

最新一次评论和随机一个评论.

[wdwen1986]

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
最新一次的

[wdwen1986]

select a.*,d.* 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
上面少了红色部分

[wdwen1986]

随机的等高人吧

[zhaoyh0530]

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