请教一个left JOIN去除重复的问题

[a11529182]

我原来的是
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条数据,但是会把有重复的也显示出来

PE_Article
中的ArticleID ChannelID ClassID Title TitleIntact Subheading Author CopyFrom Inputer LinkUrl Editor Keyword Hits CommentCount UpdateTime CreateTime OnTop Elite Status Content IncludePic DefaultPicUrl UploadFiles InfoPoint PaginationType Deleted SkinID TemplateID Stars TitleFontColor TitleFontType MaxCharPerPage ShowCommentLink Receive ReceiveUser Received AutoReceiveTime ReceiveType Intro PresentExp Copymoney IsPayed Beneficiary PayDate VoteID InfoPurview arrGroupID ChargeType PitchTime ReadTimes DividePercent BlogID

pe_comment
中是 CommentID ModuleType InfoID UserType UserName Sex OICQ MSN ICQ Email Homepage IP WriteTime Score Content ReplyName ReplyContent ReplyTime Passed


不知道高手们看的懂不,我是新手,让我整理太难了, InfoID中如果有重复数据的话,会显示重复数据,我觉得就是应该在PE_Comment.InfoID=PE_Article.ArticleID比较之前就把InfoID中重复的值去掉,请高手帮忙写个列子吧,access数据库

[正宗老冉]

加 DISTINCT 不行吗?

[中国风]

http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html--處理方法top 1/min/max

[a11529182]

我在网上找了一下午的文章了
好像是说DISTINCT不支持access
麻烦写一下,DISTINCT加在哪?

[中国风]

05可用
SELECT  top 8 * 
from 
	PE_Article  a
outer  apply  
	(select top 1 * from PE_Comment where InfoID=a.ArticleID) b 
where 
	ChannelID=1002  
Order By ArticleID desc

[a11529182]

2楼的文章好长,我真的不会啊,看不懂

[sharpsoftgroup]

不好意思，不太明白你说的重复是两条数据完全相同还是只是InfoID相同，而其他列中存在不相同的值。
如果是第二种情况，按你说的"在PE_Comment.InfoID=PE_Article.ArticleID比较之前就把InfoID中重复的值去掉",即可。不过我觉得你给的信息不太够，没法写这个语句。
若是第一种情况，在你的代码上加上“distinct ”即可。
SELECT distinct top 8 * from PE_Article left JOIN PE_Comment on PE_Comment.InfoID=PE_Article.ArticleID where ChannelID=1002 Order By ArticleID desc
--以上观点如果错了请指正，共同进步

[a11529182]

中国疯,你给的查询语句提示:FROM 子句语法错误。


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中不为空的数据了

[a11529182]

抱歉是中国风,紫光输入法害人

[中国风]

SELECT  top 8 * from PE_Article  
left JOIN 
PE_Comment on PE_Comment.InfoID=PE_Article.ArticleID and CommentID in(select  max(CommentID)  as  CommentID  
from  PE_Comment  group  by  InfoID)
where ChannelID=1002  Order By ArticleID desc

放在left join 后面
最好改為以下方式

SELECT  top 8 * 
from PE_Article  a
left JOIN 
(select InfoID max(CommentID)CommentID  as  CommentID  from  PE_Comment  group  by  InfoID) --指定要顯示的列
on b.InfoID=a.ArticleID 
where ChannelID=1002  Order By ArticleID desc

[a11529182]

我需要的就是在PE_Comment.InfoID=PE_Article.ArticleID比较之前就把InfoID中重复的值去掉,
SELECT distinct top 8 * from PE_Article left JOIN PE_Comment on PE_Comment.InfoID=PE_Article.ArticleID where ChannelID=1002 Order By ArticleID desc
没去掉重复的值
请问你需要什么信息
我这么写SELECT top 8 ArticleID,ChannelID,Title from PE_Article left JOIN PE_Comment on PE_Comment.InfoID=PE_Article.ArticleID where ChannelID=1002 Order By ArticleID desc
就查不出PE_Comment的数据了,请问怎么能把PE_Comment中的数据也写出来

[a11529182]

中国风老大,麻烦直接复制粘贴,我修改了半天还是FROM 子句语法错误。

[中国风]

PE_Comment 表中InfoID列重復時，先去掉重復再連
PE_Article 表中ArticleID列重復時，先去掉重復再連

用以上方法就行了，
05可用outer apply的方式取

[a11529182]

SELECT top 8 * from PE_Article a left JOIN (select InfoID max(CommentID)CommentID as CommentID from PE_Comment group by InfoID) on b.InfoID=a.ArticleID where ChannelID=1002 Order By ArticleID desc


JOIN 操作语法错误。

[a11529182]

http://leiyanhao.host.080idc.cn/test/1.mdb
数据库下载地址,不好意思,26M

[a11529182]

SELECT top 8 * from PE_Article left JOIN PE_Comment on PE_Comment.InfoID=PE_Article.ArticleID and CommentID in(select max(CommentID) as CommentID from PE_Comment group by InfoID) where ChannelID=1002 Order By ArticleID desc
这一句查询错误提示是语法错误。 在查询表达式 '' 中。

[中国风]

mdb--是不是Access库

SELECT  top 8 * 
from 
PE_Article  a 
left JOIN (select InfoID max(CommentID)CommentID  as  CommentID  from  PE_Comment  group  by  InfoID) b--加上别名表b
on b.InfoID=a.ArticleID 
where ChannelID=1002  Order By ArticleID desc 

[nzperfect]

楼上手误了，这么写取不到PE_Comment的很多字段。

[中国风]

[Quote=引用 17 楼 perfectaction 的回复:]
楼上手误了，这么写取不到PE_Comment的很多字段。
[/Quote]

在9楼有注释"指定显示列"

SELECT  top 8 * 
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