right join 和（+）

来源：互联网发布：淘宝复刻潮牌店铺排行编辑：程序博客网时间：2024/05/14 19:06

今天遇到个问题，是有关oracle中的right join 和（+）

很奇怪的问题看了很久，才知道是哪的问题

--right join

select /*l.trade_city_code as city_code ,*/
                       t.trade_type as trade_type,
count(distinct l.trade_id) as count
                  from tf_bh_trade l
                 right join (select *
                              from uop_crm3.td_s_tradetype y
                             where y.trade_type_code in
                                   (127, 710, 532, 1029, 1025, 149, 141, 100, 419,
                                   1275, 80, 1274, 615, 416, 1060,124, 311, 1028, 116, 330,
                                    310, 126, 122, 12, 137, 143, 128, 279, 278, 136,
                                    110, 133, 70, 139, 31, 290, 30, 250, 131, 381, 690,
                                    72, 60, 20, 190, 132, 120, 160, 268, 269, 270, 271, 272,
                                    273, 274, 275, 276, 277, 192, 71, 340, 63)) t
                                    on l.trade_type_code =   t.trade_type_code
                                      and t.eparchy_code ='0312' and l.eparchy_code ='0312'
and l.trade_staff_id <> 'SUPERUSR'
and l.trade_type_code <= 7000
and l.accept_date >=
to_date(to_char(trunc(sysdate-1),'yyyy-mm-dd') ||' 00:00:01','yyyy-mm-dd hh24:mi:ss')
and accept_date <
to_date(to_char(trunc(sysdate-1),'yyyy-mm-dd') ||' 23:59:59','yyyy-mm-dd hh24:mi:ss')
                 group by /*l.trade_city_code,*/t.trade_type
                 order by /*l.trade_city_code,*/t.trade_type;

--（+）

select /*l.trade_city_code as city_code ,*/
                       t.trade_type as trade_type,
count(distinct l.trade_id) as count
                  from uop_crm3.tf_bh_trade l
               , (select *
                              from uop_crm3.td_s_tradetype y
                             where y.trade_type_code in
                                   (127, 710, 532, 1029, 1025, 149, 141, 100, 419,
                                   1275, 80, 1274, 615, 416, 1060,124, 311, 1028, 116, 330,
                                    310, 126, 122, 12, 137, 143, 128, 279, 278, 136,
                                    110, 133, 70, 139, 31, 290, 30, 250, 131, 381, 690,
                                    72, 60, 20, 190, 132, 120, 160, 268, 269, 270, 271, 272,
                                    273, 274, 275, 276, 277, 192, 71, 340, 63)
                                    and y.eparchy_code ='0312'
                                    ) t
                                    where l.trade_type_code(+) = t.trade_type_code
                                      and l.eparchy_code ='0312'
and l.trade_staff_id <> 'SUPERUSR'
and l.trade_type_code <= 7000
and l.accept_date >=
to_date(to_char(trunc(sysdate-1),'yyyy-mm-dd') ||' 00:00:01','yyyy-mm-dd hh24:mi:ss')
and l.accept_date <
to_date(to_char(trunc(sysdate-1),'yyyy-mm-dd') ||' 23:59:59','yyyy-mm-dd hh24:mi:ss')
                 group by /*l.trade_city_code,*/t.trade_type
                 order by /*l.trade_city_code,*/t.trade_type;

上面这两个语句乍一看，执行结果应该是一致的，但其实不然，然后我就各种揣摩，咨询别人，后来终于知道错误之处了

以下语句是修改之后的

--（+）

select /*l.trade_city_code as city_code ,*/
                       t.trade_type as trade_type,
count(distinct l.trade_id) as count
                  from (SELECT * FROM uop_crm3.tf_bh_trade l
                   where l.eparchy_code ='0312'
and l.trade_staff_id <> 'SUPERUSR'
and l.trade_type_code <= 7000
and l.accept_date >=
to_date(to_char(trunc(sysdate-1),'yyyy-mm-dd') ||' 00:00:01','yyyy-mm-dd hh24:mi:ss')
and l.accept_date <
to_date(to_char(trunc(sysdate-1),'yyyy-mm-dd') ||' 23:59:59','yyyy-mm-dd hh24:mi:ss'))l
               , (select *
                              from uop_crm3.td_s_tradetype y
                             where y.trade_type_code in
                                   (127, 710, 532, 1029, 1025, 149, 141, 100, 419,
                                   1275, 80, 1274, 615, 416, 1060,124, 311, 1028, 116, 330,
                                    310, 126, 122, 12, 137, 143, 128, 279, 278, 136,
                                    110, 133, 70, 139, 31, 290, 30, 250, 131, 381, 690,
                                    72, 60, 20, 190, 132, 120, 160, 268, 269, 270, 271, 272,
                                    273, 274, 275, 276, 277, 192, 71, 340, 63)
                                    and y.eparchy_code ='0312'
                                    ) t --受理动作
                                    where l.trade_type_code(+) = t.trade_type_code

                 group by /*l.trade_city_code,*/t.trade_type
                 order by /*l.trade_city_code,*/t.trade_type;

原来，跟where条件语句的放置位置是有关系的，即这句where l.eparchy_code ='0312'
and l.trade_staff_id <> 'SUPERUSR'
and l.trade_type_code <= 7000
and l.accept_date >=
to_date(to_char(trunc(sysdate-1),'yyyy-mm-dd') ||' 00:00:01','yyyy-mm-dd hh24:mi:ss')
and l.accept_date <
to_date(to_char(trunc(sysdate-1),'yyyy-mm-dd') ||' 23:59:59','yyyy-mm-dd hh24:mi:ss'))，虽然不太清楚原理，但确实解决问题了,继续学习ing...