本人菜鸟,多多包涵。 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 字段是可以重复的,所以会出现重复的数据,现在我想如何进行查询可以将重复的数据只查询一条出来
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 字段是可以重复的,所以会出现重复的数据,现在我想如何进行查询可以将重复的数据只查询一条出来
解决方案 »
- 请教关于group by的一个疑问
- 关于数据库的关闭
- 用什么sql可以快速的看出三张表主外键之间的关系呢?要查看那张表
- 分组,求和与统计的查询,急需.
- oracle8.1.6 安装提示ora-27102:out of memory
- 在pl/sql->command window 中执行后并没有输出结果呀?
- 请问在redhat 8 linux下oracle9i 怎样运行(已经安装成功)
- 请问这个SQL语句怎么写?
- 如何在SQL*PLUS 中调用ORacle的存储过程???以及在delphi的query中如何调用!!
- 无法配置Net8!!!!!!!请帮忙!!!!!!!!
- Clob中如何分行读取
- oracle小高技术开发
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')
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')这位仁兄,还是不行,查出来还是779条,没有减少,还是谢谢
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')这位仁兄,还是不行,查出来还是779条,没有减少,还是谢谢
看看你的查询,在t_Inspection_Condition_Hist表中
用到了Hist.Item_Id和Hist.Inspection_Hist_Id,你Inspection_Hist_一样的值但IdItem_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')这位仁兄,还是不行,查出来还是779条,没有减少,还是谢谢
看看你的查询,在t_Inspection_Condition_Hist表中
用到了Hist.Item_Id和Hist.Inspection_Hist_Id,你Inspection_Hist_一样的值但IdItem_Id会不同啊
其实是这样的:
可以这样理解,就是一条数据有多重情况的Item_Id,也就是说Item_Id的值有多个,所以存在t_Inspection_Condition_Hist表中的时候前面的字段都是一样的,只有Item_Id的值不同,然后关联查询的时候那些重复的也满足查询条件所以全部查询出来了。
我想的是只需要一个Item_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, 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')
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')
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')
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')
我这里是通过查询其中一个表中的全部列信息,然后把另外一个表的多余信息放上去的。