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

zhougonghenbang 2009-06-30 08:32:16

假设:

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

...全文

89 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

zhaoyh0530 2009-06-30

打赏
举报

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

wdwen1986 2009-06-30

打赏
举报

随机的等高人吧

wdwen1986 2009-06-30

打赏
举报

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 2009-06-30

打赏
举报

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

zhougonghenbang 2009-06-30