select a.items_id,b.RPS_DATE_T from (
select '777808' as items_id from dual
)a left join (
select f.ITEMS_ID ,min(f.RPS_DATE_T) as RPS_DATE_T
from DWKCFX.DW_TX_KCFX_CP_DAY_RPS_DT f
left join (
select ITEMS_ID,max(to_date(RPS_DATE_T,'yyyy-mm-dd')) as RPS_DATE_T
from DWKCFX.DW_TX_KCFX_CP_DAY_RPS_DT
where to_date(RPS_DATE_T,'yyyy-mm-dd') < to_date('2008-06-01','yyyy-mm-dd') and TODAY_PAY_INV > 0
group by ITEMS_ID
)g on f.ITEMS_ID = g.ITEMS_ID
where to_date(f.RPS_DATE_T,'yyyy-mm-dd') <= to_date('2008-06-01','yyyy-mm-dd') and f.END_INV > 0
and to_date(f.RPS_DATE_T,'yyyy-mm-dd') > nvl(g.RPS_DATE_T,to_date('1900-01-01','yyyy-mm-dd'))
and f.items_id='777808'
group by f.ITEMS_ID
)b on a.items_id = b.items_id红色部分单独查询的结果是 '777808','2008-06-01'而全部SQL查询的结果是 '777808','2006-12-09',正确应该是 '777808','2008-06-01'和同事调试了很久,觉得很怪异,知道什么原因的朋友帮忙看下,谢谢。
select '777808' as items_id from dual
)a left join (
select f.ITEMS_ID ,min(f.RPS_DATE_T) as RPS_DATE_T
from DWKCFX.DW_TX_KCFX_CP_DAY_RPS_DT f
left join (
select ITEMS_ID,max(to_date(RPS_DATE_T,'yyyy-mm-dd')) as RPS_DATE_T
from DWKCFX.DW_TX_KCFX_CP_DAY_RPS_DT
where to_date(RPS_DATE_T,'yyyy-mm-dd') < to_date('2008-06-01','yyyy-mm-dd') and TODAY_PAY_INV > 0
group by ITEMS_ID
)g on f.ITEMS_ID = g.ITEMS_ID
where to_date(f.RPS_DATE_T,'yyyy-mm-dd') <= to_date('2008-06-01','yyyy-mm-dd') and f.END_INV > 0
and to_date(f.RPS_DATE_T,'yyyy-mm-dd') > nvl(g.RPS_DATE_T,to_date('1900-01-01','yyyy-mm-dd'))
and f.items_id='777808'
group by f.ITEMS_ID
)b on a.items_id = b.items_id红色部分单独查询的结果是 '777808','2008-06-01'而全部SQL查询的结果是 '777808','2006-12-09',正确应该是 '777808','2008-06-01'和同事调试了很久,觉得很怪异,知道什么原因的朋友帮忙看下,谢谢。
select a.items_id,b.RPS_DATE_T from (
select '777808' as items_id from dual
)a left join ( select '777808' items_id,'2008-06-01' RPS_DATE_T
)b on a.items_id = b.items_id
select '777808' as items_id from dual
)a left join ( select '777808' items_id,'2008-06-01' RPS_DATE_T from dual
)b on a.items_id = b.items_id ---------执行结果:
777808 2008-06-01所以楼主的内部红色的select如果执行结果真是'777808','2008-06-01' 的话,是绝对不会出现“ '777808','2006-12-09',”结果的。楼主可以将
select a.items_id,b.RPS_DATE_T from (
select '777808' as items_id from dual
)a left join ( select '777808' items_id,'2008-06-01' RPS_DATE_T from dual
)b on a.items_id = b.items_id 以及
select a.items_id,b.RPS_DATE_T from (
select '777808' as items_id from dual
)a left join (
select f.ITEMS_ID ,min(f.RPS_DATE_T) as RPS_DATE_T
from DWKCFX.DW_TX_KCFX_CP_DAY_RPS_DT f
left join (
select ITEMS_ID,max(to_date(RPS_DATE_T,'yyyy-mm-dd')) as RPS_DATE_T
from DWKCFX.DW_TX_KCFX_CP_DAY_RPS_DT
where to_date(RPS_DATE_T,'yyyy-mm-dd') < to_date('2008-06-01','yyyy-mm-dd') and TODAY_PAY_INV > 0
group by ITEMS_ID
)g on f.ITEMS_ID = g.ITEMS_ID
where to_date(f.RPS_DATE_T,'yyyy-mm-dd') <= to_date('2008-06-01','yyyy-mm-dd') and f.END_INV > 0
and to_date(f.RPS_DATE_T,'yyyy-mm-dd') > nvl(g.RPS_DATE_T,to_date('1900-01-01','yyyy-mm-dd'))
and f.items_id='777808'
group by f.ITEMS_ID
)b on a.items_id = b.items_id 还有
select f.ITEMS_ID ,min(f.RPS_DATE_T) as RPS_DATE_T
from DWKCFX.DW_TX_KCFX_CP_DAY_RPS_DT f
left join (
select ITEMS_ID,max(to_date(RPS_DATE_T,'yyyy-mm-dd')) as RPS_DATE_T
from DWKCFX.DW_TX_KCFX_CP_DAY_RPS_DT
where to_date(RPS_DATE_T,'yyyy-mm-dd') < to_date('2008-06-01','yyyy-mm-dd') and TODAY_PAY_INV > 0
group by ITEMS_ID
)g on f.ITEMS_ID = g.ITEMS_ID
where to_date(f.RPS_DATE_T,'yyyy-mm-dd') <= to_date('2008-06-01','yyyy-mm-dd') and f.END_INV > 0
and to_date(f.RPS_DATE_T,'yyyy-mm-dd') > nvl(g.RPS_DATE_T,to_date('1900-01-01','yyyy-mm-dd'))
and f.items_id='777808'
group by f.ITEMS_ID 一起执行下,看三者的值是多少?
还有可能就是楼主执行红色部分select的数据库实例 跟执行全部select语句的数据库实例不是一个实例,这种情况结果是不一样的,就很有可能啦!
左连接:left join左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值(null)。
右连接:right join 右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。
select a.items_id,b.RPS_DATE_T from (
select '777808' as items_id from dual
)a left join ( select '777808' items_id,'2008-06-01' RPS_DATE_T from dual
)b on a.items_id = b.items_id 结果是:'777808','2008-06-01' ,我也这样测试过,是没有问题的。select a.items_id,b.RPS_DATE_T from (
select '777808' as items_id from dual
)a left join (
select f.ITEMS_ID ,min(f.RPS_DATE_T) as RPS_DATE_T
from DWKCFX.DW_TX_KCFX_CP_DAY_RPS_DT f
left join (
select ITEMS_ID,max(to_date(RPS_DATE_T,'yyyy-mm-dd')) as RPS_DATE_T
from DWKCFX.DW_TX_KCFX_CP_DAY_RPS_DT
where to_date(RPS_DATE_T,'yyyy-mm-dd') < to_date('2008-06-01','yyyy-mm-dd') and TODAY_PAY_INV > 0
group by ITEMS_ID
)g on f.ITEMS_ID = g.ITEMS_ID
where to_date(f.RPS_DATE_T,'yyyy-mm-dd') <= to_date('2008-06-01','yyyy-mm-dd') and f.END_INV > 0
and to_date(f.RPS_DATE_T,'yyyy-mm-dd') > nvl(g.RPS_DATE_T,to_date('1900-01-01','yyyy-mm-dd'))
and f.items_id='777808'
group by f.ITEMS_ID
)b on a.items_id = b.items_id 结果是:'777808','2006-12-09' select f.ITEMS_ID ,min(f.RPS_DATE_T) as RPS_DATE_T
from DWKCFX.DW_TX_KCFX_CP_DAY_RPS_DT f
left join (
select ITEMS_ID,max(to_date(RPS_DATE_T,'yyyy-mm-dd')) as RPS_DATE_T
from DWKCFX.DW_TX_KCFX_CP_DAY_RPS_DT
where to_date(RPS_DATE_T,'yyyy-mm-dd') < to_date('2008-06-01','yyyy-mm-dd') and TODAY_PAY_INV > 0
group by ITEMS_ID
)g on f.ITEMS_ID = g.ITEMS_ID
where to_date(f.RPS_DATE_T,'yyyy-mm-dd') <= to_date('2008-06-01','yyyy-mm-dd') and f.END_INV > 0
and to_date(f.RPS_DATE_T,'yyyy-mm-dd') > nvl(g.RPS_DATE_T,to_date('1900-01-01','yyyy-mm-dd'))
and f.items_id='777808'
group by f.ITEMS_ID 结果是:'777808',2008-06-01'是在同一个实例执行的,网上搜了一下,会不会是ORACLE解析器的问题?
select '777808' as items_id from dual
)a left join (
select f.ITEMS_ID ,min(f.RPS_DATE_T) as RPS_DATE_T
from DWKCFX.DW_TX_KCFX_CP_DAY_RPS_DT f
left join (
select ITEMS_ID,max(to_date(RPS_DATE_T,'yyyy-mm-dd')) as RPS_DATE_T
from DWKCFX.DW_TX_KCFX_CP_DAY_RPS_DT
where to_date(RPS_DATE_T,'yyyy-mm-dd') < to_date('2008-06-01','yyyy-mm-dd') and TODAY_PAY_INV > 0
group by ITEMS_ID
)g on f.ITEMS_ID = g.ITEMS_ID
where to_date(f.RPS_DATE_T,'yyyy-mm-dd') < to_date('2008-06-02','yyyy-mm-dd') and f.END_INV > 0
and to_date(f.RPS_DATE_T,'yyyy-mm-dd') > nvl(g.RPS_DATE_T,to_date('1900-01-01','yyyy-mm-dd'))
and f.items_id='777808'
group by f.ITEMS_ID
)b on a.items_id = b.items_id 我看到一个日期的判断里面有=符号,我给改成了< 然后'2008-06-01'变成了'2008-06-02'。你再执行下,看结果是否正常呢?