SELECT top 8 * from PE_Article left JOIN PE_Comment on PE_Comment.InfoID=PE_Article.ArticleID where ChannelID=1002 Order By ArticleID desc
可以查询出8条数据,但是会把有重复的也显示出来SELECT top 8 * from PE_Article left JOIN PE_Comment on PE_Comment.InfoID=PE_Article.ArticleID where ChannelID=1002 and CommentID in(select max(CommentID) as CommentID from PE_Comment group by InfoID) Order By ArticleID desc 这样写到是没有重复了,但是只能显示3条了,就是只能显示pe_comment中不为空的数据了http://leiyanhao.host.080idc.cn/test/1.mdb 数据库的地址
可以查询出8条数据,但是会把有重复的也显示出来SELECT top 8 * from PE_Article left JOIN PE_Comment on PE_Comment.InfoID=PE_Article.ArticleID where ChannelID=1002 and CommentID in(select max(CommentID) as CommentID from PE_Comment group by InfoID) Order By ArticleID desc 这样写到是没有重复了,但是只能显示3条了,就是只能显示pe_comment中不为空的数据了http://leiyanhao.host.080idc.cn/test/1.mdb 数据库的地址
select top 8 *
from PE_Article as a
left join
(
select a.* from PE_Comment as a inner join
(select max(CommentID) as CommentID from PE_Comment group by InfoID) as b on a.CommentID=b.CommentID
) as b on b.InfoID=a.ArticleID where ChannelID=1002
select max(CommentID) as CommentID from PE_Comment group by InfoID
不只有18 19 20 三个吗,
and CommentID in(select max(CommentID) as CommentID from PE_Comment group by InfoID)
是使最终只有三条记录的原因.
from
PE_Article a
left JOIN
(select * from PE_Comment b where CommentID=(select max(CommentID) from PE_Comment where InfoID=b.InfoID)) b on b.InfoID=a.ArticleID
where
ChannelID=1002 Order By ArticleID desc 这样就行了,Access不支持连三个表
from
PE_Article a
left JOIN
(select * from PE_Comment b where CommentID in (select max(CommentID) from PE_Comment group by InfoID)) b on b.InfoID=a.ArticleID
where
ChannelID=1002 Order By ArticleID desc
那是SQL运行的,在Access不支持
只有三条记录的原因正如8楼所说.不知道楼主想要什么样的效果?
on t.InfoID=PE_Article.ArticleID where ChannelID=1002 Order By ArticleID desc