连接查询如何去掉重复的数据

eggerle 2013-09-24 01:41:10

本人菜鸟，多多包涵。

Select Rcxc.Inspection_Id As Inspection_Id, Rcxc.*
From t_Inspection_Hist Rcxc
Where Rcxc.Inspection_Date >= To_Date('2012-09-23', 'yyyy-MM-dd') And
Rcxc.Inspection_Date <= To_Date('2013-09-23', 'yyyy-MM-dd')

本人通过上面的查询可以得到500条数据，可是关联t_Inspection_Condition_Hist表后数据会达到779条，

查询语句如下：
Select Distinct Rcxc.Inspection_Id As Inspection_Id, Rcxc.*, Hist.Item_Id
From t_Inspection_Hist Rcxc
Left Join t_Inspection_Condition_Hist Hist On Hist.Inspection_Hist_Id = Rcxc.Id
Where Rcxc.Inspection_Date >= To_Date('2012-09-23', 'yyyy-MM-dd') And
Rcxc.Inspection_Date <= To_Date('2013-09-23', 'yyyy-MM-dd')

因为在t_Inspection_Condition_Hist中Inspection_Hist_Id 字段是可以重复的，所以会出现重复的数据，现在我想如何进行查询可以将重复的数据只查询一条出来

...全文

908 17 打赏收藏转发到动态举报

写回复

用AI写文章

17 条回复

切换为时间正序

请发表友善的回复…

发表回复

baidu_28246729 2015-05-16

打赏
举报

SELECT distinct s.*,c.Score FROM Student s join SC c on s.Sno=c.Sno 我这里是通过查询其中一个表中的全部列信息，然后把另外一个表的多余信息放上去的。

jerrygr_ok 2013-10-08

打赏
举报

或者这样也可以：


Select Distinct Rcxc.Inspection_Id As Inspection_Id, Rcxc.*, Hist.Item_Id
From t_Inspection_Hist Rcxc Left Join 
(select distinct(Inspection_Hist_Id), Item_Id from t_Inspection_Condition_Hist) Hist 
On Hist.Inspection_Hist_Id = Rcxc.Id
Where Rcxc.Inspection_Date >= To_Date('2012-09-23', 'yyyy-MM-dd') And Rcxc.Inspection_Date <= To_Date('2013-09-23', 'yyyy-MM-dd')

jerrygr_ok 2013-10-08

打赏
举报


Select Distinct Rcxc.Inspection_Id As Inspection_Id, Rcxc.*, Hist.Item_Id
From t_Inspection_Hist Rcxc Left Join 
(select Inspection_Hist_Id, Item_Id, count(distinct Inspection_Hist_Id) from t_Inspection_Condition_Hist group by Inspection_Hist_Id, Item_Id) Hist 
On Hist.Inspection_Hist_Id = Rcxc.Id
Where Rcxc.Inspection_Date >= To_Date('2012-09-23', 'yyyy-MM-dd') And Rcxc.Inspection_Date <= To_Date('2013-09-23', 'yyyy-MM-dd')

依凡-YAO 2013-10-04

打赏
举报

row_number分析函数也可以

依凡-YAO 2013-10-04

打赏
举报

使用rowid

lixin5678 2013-10-04

打赏
举报

这应该可以吧 Select Distinct Rcxc.Inspection_Id As Inspection_Id, Rcxc.*, (select /*+ index_ffs(Hist) */ max(Item_Id) from Hist where Inspection_Hist_Id = Rcxc.Id) as Item_Id From t_Inspection_Hist Rcxc Where Rcxc.Inspection_Date >= To_Date('2012-09-23', 'yyyy-MM-dd') And Rcxc.Inspection_Date <= To_Date('2013-09-23', 'yyyy-MM-dd')

question1375050 2013-10-02

打赏
举报

用distinct不可以吗

无敌小二傻 2013-09-24

打赏
举报

那你用这个试试吧：


Select Distinct Rcxc.Inspection_Id As Inspection_Id, Rcxc.*, Hist.Item_Id
  From t_Inspection_Hist Rcxc
  Left Join (
  select distinct Inspection_Hist_Id, max(Item_Id)over(partition by Inspection_Hist_Id) from t_Inspection_Condition_Hist
  ) Hist
  On Hist.Inspection_Hist_Id = Rcxc.Id
  Where Rcxc.Inspection_Date >= To_Date('2012-09-23', 'yyyy-MM-dd')
  And Rcxc.Inspection_Date <= To_Date('2013-09-23', 'yyyy-MM-dd')

eggerle 2013-09-24

打赏
举报

引用 8 楼 lyliu602 的回复:

[quote=引用 5 楼 eggerle 的回复:] [quote=引用 4 楼 lyliu602 的回复:] [quote=引用楼主 eggerle 的回复:] 本人菜鸟，多多包涵。 Select Rcxc.Inspection_Id As Inspection_Id, Rcxc.* From t_Inspection_Hist Rcxc Where Rcxc.Inspection_Date >= To_Date('2012-09-23', 'yyyy-MM-dd') And Rcxc.Inspection_Date <= To_Date('2013-09-23', 'yyyy-MM-dd') 本人通过上面的查询可以得到500条数据，可是关联t_Inspection_Condition_Hist表后数据会达到779条，查询语句如下： Select Distinct Rcxc.Inspection_Id As Inspection_Id, Rcxc.*, Hist.Item_Id From t_Inspection_Hist Rcxc Left Join t_Inspection_Condition_Hist Hist On Hist.Inspection_Hist_Id = Rcxc.Id Where Rcxc.Inspection_Date >= To_Date('2012-09-23', 'yyyy-MM-dd') And Rcxc.Inspection_Date <= To_Date('2013-09-23', 'yyyy-MM-dd') 因为在t_Inspection_Condition_Hist中Inspection_Hist_Id 字段是可以重复的，所以会出现重复的数据，现在我想如何进行查询可以将重复的数据只查询一条出来


Select Distinct Rcxc.Inspection_Id As Inspection_Id, Rcxc.*, Hist.Item_Id
  From t_Inspection_Hist Rcxc
  Left Join 
  (select distinct Inspection_Hist_Id,Item_Id from t_Inspection_Condition_Hist)Hist
  On Hist.Inspection_Hist_Id = Rcxc.Id
 Where Rcxc.Inspection_Date >= To_Date('2012-09-23', 'yyyy-MM-dd')
   And Rcxc.Inspection_Date <= To_Date('2013-09-23', 'yyyy-MM-dd')

[/quote] 这位仁兄，还是不行，查出来还是779条，没有减少，还是谢谢[/quote] 看看你的查询，在t_Inspection_Condition_Hist表中用到了Hist.Item_Id和Hist.Inspection_Hist_Id，你Inspection_Hist_一样的值但IdItem_Id会不同啊[/quote] 其实是这样的：可以这样理解，就是一条数据有多重情况的Item_Id，也就是说Item_Id的值有多个，所以存在t_Inspection_Condition_Hist表中的时候前面的字段都是一样的，只有Item_Id的值不同，然后关联查询的时候那些重复的也满足查询条件所以全部查询出来了。我想的是只需要一个Item_Id的值，那么数据就不会重复了

无敌小二傻 2013-09-24

打赏
举报

引用 5 楼 eggerle 的回复:

[quote=引用 4 楼 lyliu602 的回复:] [quote=引用楼主 eggerle 的回复:] 本人菜鸟，多多包涵。 Select Rcxc.Inspection_Id As Inspection_Id, Rcxc.* From t_Inspection_Hist Rcxc Where Rcxc.Inspection_Date >= To_Date('2012-09-23', 'yyyy-MM-dd') And Rcxc.Inspection_Date <= To_Date('2013-09-23', 'yyyy-MM-dd') 本人通过上面的查询可以得到500条数据，可是关联t_Inspection_Condition_Hist表后数据会达到779条，查询语句如下： Select Distinct Rcxc.Inspection_Id As Inspection_Id, Rcxc.*, Hist.Item_Id From t_Inspection_Hist Rcxc Left Join t_Inspection_Condition_Hist Hist On Hist.Inspection_Hist_Id = Rcxc.Id Where Rcxc.Inspection_Date >= To_Date('2012-09-23', 'yyyy-MM-dd') And Rcxc.Inspection_Date <= To_Date('2013-09-23', 'yyyy-MM-dd') 因为在t_Inspection_Condition_Hist中Inspection_Hist_Id 字段是可以重复的，所以会出现重复的数据，现在我想如何进行查询可以将重复的数据只查询一条出来


Select Distinct Rcxc.Inspection_Id As Inspection_Id, Rcxc.*, Hist.Item_Id
  From t_Inspection_Hist Rcxc
  Left Join 
  (select distinct Inspection_Hist_Id,Item_Id from t_Inspection_Condition_Hist)Hist
  On Hist.Inspection_Hist_Id = Rcxc.Id
 Where Rcxc.Inspection_Date >= To_Date('2012-09-23', 'yyyy-MM-dd')
   And Rcxc.Inspection_Date <= To_Date('2013-09-23', 'yyyy-MM-dd')

eggerle 2013-09-24

打赏
举报

引用 6 楼 stublue 的回复:

楼上正解。。。。。。。。

Leon_He2014 2013-09-24

打赏
举报

楼上正解。。。。。。。。

eggerle 2013-09-24

打赏
举报

引用 4 楼 lyliu602 的回复:

[quote=引用楼主 eggerle 的回复:] 本人菜鸟，多多包涵。 Select Rcxc.Inspection_Id As Inspection_Id, Rcxc.* From t_Inspection_Hist Rcxc Where Rcxc.Inspection_Date >= To_Date('2012-09-23', 'yyyy-MM-dd') And Rcxc.Inspection_Date <= To_Date('2013-09-23', 'yyyy-MM-dd') 本人通过上面的查询可以得到500条数据，可是关联t_Inspection_Condition_Hist表后数据会达到779条，查询语句如下： Select Distinct Rcxc.Inspection_Id As Inspection_Id, Rcxc.*, Hist.Item_Id From t_Inspection_Hist Rcxc Left Join t_Inspection_Condition_Hist Hist On Hist.Inspection_Hist_Id = Rcxc.Id Where Rcxc.Inspection_Date >= To_Date('2012-09-23', 'yyyy-MM-dd') And Rcxc.Inspection_Date <= To_Date('2013-09-23', 'yyyy-MM-dd') 因为在t_Inspection_Condition_Hist中Inspection_Hist_Id 字段是可以重复的，所以会出现重复的数据，现在我想如何进行查询可以将重复的数据只查询一条出来


Select Distinct Rcxc.Inspection_Id As Inspection_Id, Rcxc.*, Hist.Item_Id
  From t_Inspection_Hist Rcxc
  Left Join 
  (select distinct Inspection_Hist_Id,Item_Id from t_Inspection_Condition_Hist)Hist
  On Hist.Inspection_Hist_Id = Rcxc.Id
 Where Rcxc.Inspection_Date >= To_Date('2012-09-23', 'yyyy-MM-dd')
   And Rcxc.Inspection_Date <= To_Date('2013-09-23', 'yyyy-MM-dd')

[/quote] 这位仁兄，还是不行，查出来还是779条，没有减少，还是谢谢

无敌小二傻 2013-09-24

打赏
举报

引用楼主 eggerle 的回复:

本人菜鸟，多多包涵。 Select Rcxc.Inspection_Id As Inspection_Id, Rcxc.* From t_Inspection_Hist Rcxc Where Rcxc.Inspection_Date >= To_Date('2012-09-23', 'yyyy-MM-dd') And Rcxc.Inspection_Date <= To_Date('2013-09-23', 'yyyy-MM-dd') 本人通过上面的查询可以得到500条数据，可是关联t_Inspection_Condition_Hist表后数据会达到779条，查询语句如下： Select Distinct Rcxc.Inspection_Id As Inspection_Id, Rcxc.*, Hist.Item_Id From t_Inspection_Hist Rcxc Left Join t_Inspection_Condition_Hist Hist On Hist.Inspection_Hist_Id = Rcxc.Id Where Rcxc.Inspection_Date >= To_Date('2012-09-23', 'yyyy-MM-dd') And Rcxc.Inspection_Date <= To_Date('2013-09-23', 'yyyy-MM-dd') 因为在t_Inspection_Condition_Hist中Inspection_Hist_Id 字段是可以重复的，所以会出现重复的数据，现在我想如何进行查询可以将重复的数据只查询一条出来


Select Distinct Rcxc.Inspection_Id As Inspection_Id, Rcxc.*, Hist.Item_Id
  From t_Inspection_Hist Rcxc
  Left Join 
  (select distinct Inspection_Hist_Id,Item_Id from t_Inspection_Condition_Hist)Hist
  On Hist.Inspection_Hist_Id = Rcxc.Id
 Where Rcxc.Inspection_Date >= To_Date('2012-09-23', 'yyyy-MM-dd')
   And Rcxc.Inspection_Date <= To_Date('2013-09-23', 'yyyy-MM-dd')

eggerle 2013-09-24