union 和order by 连接使用报错的问题

今天写sql的时候，遇到一个奇怪的现象，分别执行的时候可以成功执行，但是union连接之后却会报错。

--前面的sql

select count(distinct u.trade_id) as count, u.trade_type as trade_type
  from (select distinct (l.trade_id) as trade_id, t.trade_type as trade_type
          from uop_crm3.tf_b_trade l
         right join (select *
                      from uop_crm3.td_s_tradetype y
                     where y.trade_type_code in (71, 340, 63)) t on l.trade_type_code =
                                                                    t.trade_type_code
                                                                and l.net_type_code =
                                                                    t.net_type_code
        union
       
        select distinct (l.trade_id) as trade_id, t.trade_type as trade_type
          from uop_crm3.tf_bh_trade l
         right join (select *
                       from uop_crm3.td_s_tradetype y
where y.trade_type_code in (71, 340, 63)) t on l.trade_type_code =
                                                                     t.trade_type_code
                                                                 and l.net_type_code =
                                                                     t.net_type_code
       
        ) u
group by trade_type
order by trade_type

--后面的sql

select count(distinct uu.trade_id) as count,
       '开户-' || uu.net_type_name as trade_type
  from (select distinct (l.trade_id) as trade_id,
                        g.net_type_name as net_type_name
          from uop_crm3.tf_b_trade l, uop_crm3.td_s_netcode g
         where l.net_type_code = g.net_type_code
           and l.trade_type_code = '10'
           and l.eparchy_code = '0319'
           and l.net_type_code in ('16', '17', '33', '10', '40', '30')
        union
        select distinct (l.trade_id) as trade_id,
                        g.net_type_name as net_type_name
          from uop_crm3.tf_bh_trade l, uop_crm3.td_s_netcode g
         where l.net_type_code = g.net_type_code
           and l.trade_type_code = '10'
           and l.net_type_code in ('16', '17', '33', '10', '40', '30')) uu
group by net_type_name
order by trade_type

以上两个sql有同样的表结构，都具有两列，且类型一致，分别执行的时候没有任何问题，但是用union关联之后，PL/SQL 就会报错，找原因找找了好好久，最后终于发现错误的地方，原来是union与order by 使用不当惹得祸。

错误的语句是这样的：

select count(distinct u.trade_id) as count, u.trade_type as trade_type
  from (select distinct (l.trade_id) as trade_id, t.trade_type as trade_type
          from uop_crm3.tf_b_trade l
         right join (select *
                      from uop_crm3.td_s_tradetype y
                     where y.trade_type_code in (71, 340, 63)) t on l.trade_type_code =
                                                                    t.trade_type_code
                                                                and l.net_type_code =
                                                                    t.net_type_code
        union
       
        select distinct (l.trade_id) as trade_id, t.trade_type as trade_type
          from uop_crm3.tf_bh_trade l
         right join (select *
                       from uop_crm3.td_s_tradetype y
                      where y.trade_type_code in (71, 340, 63)) t on l.trade_type_code =
                                                                     t.trade_type_code
                                                                 and l.net_type_code =
                                                                     t.net_type_code
       
        ) u
group by trade_type
order by trade_type
union

--后面的sql

select count(distinct uu.trade_id) as count,
       '开户-' || uu.net_type_name as trade_type
  from (select distinct (l.trade_id) as trade_id,
                        g.net_type_name as net_type_name
          from uop_crm3.tf_b_trade l, uop_crm3.td_s_netcode g
         where l.net_type_code = g.net_type_code
           and l.trade_type_code = '10'
           and l.eparchy_code = '0319'
           and l.net_type_code in ('16', '17', '33', '10', '40', '30')
        union
        select distinct (l.trade_id) as trade_id,
                        g.net_type_name as net_type_name
          from uop_crm3.tf_bh_trade l, uop_crm3.td_s_netcode g
         where l.net_type_code = g.net_type_code
           and l.trade_type_code = '10'
           and l.net_type_code in ('16', '17', '33', '10', '40', '30')) uu
group by net_type_name
order by trade_type

修改之后的语句如下：
SELECT *
  FROM (select count(distinct u.trade_id) as count,
               u.trade_type as trade_type
          from (select distinct (l.trade_id) as trade_id,
                                t.trade_type as trade_type
                  from uop_crm3.tf_b_trade l
                 right join (select *
                              from uop_crm3.td_s_tradetype y
                             where y.trade_type_code in (71, 340, 63)) t on l.trade_type_code =
                                                                            t.trade_type_code
                                                                        and l.net_type_code =
                                                                            t.net_type_code
                union
               
                select distinct (l.trade_id) as trade_id,
                                t.trade_type as trade_type
                  from uop_crm3.tf_bh_trade l
                 right join (select *
                               from uop_crm3.td_s_tradetype y
                              where y.trade_type_code in (71, 340, 63)) t on l.trade_type_code =
                                                                             t.trade_type_code
                                                                         and l.net_type_code =
                                                                             t.net_type_code
               
                ) u
         group by trade_type
       
        union
        select count(distinct uu.trade_id) as count,
               '开户-' || uu.net_type_name as trade_type
          from (select distinct (l.trade_id) as trade_id,
                                g.net_type_name as net_type_name
                  from uop_crm3.tf_b_trade l, uop_crm3.td_s_netcode g
                 where l.net_type_code = g.net_type_code
                   and l.trade_type_code = '10'
                   and l.eparchy_code = '0319'
                   and l.net_type_code in
                       ('16', '17', '33', '10', '40', '30')
                union
                select distinct (l.trade_id) as trade_id,
                                g.net_type_name as net_type_name
                  from uop_crm3.tf_bh_trade l, uop_crm3.td_s_netcode g
                 where l.net_type_code = g.net_type_code
                   and l.trade_type_code = '10'
                   and l.net_type_code in
                       ('16', '17', '33', '10', '40', '30')) uu
         group by net_type_name)
order by trade_type

原来错误之处就在于union与oerder by的使用位置，union之后才可以使用order by ,查询语句继续零基础学习中。相信每天一点一滴的积累，再菜鸟的我，慢慢也会有所收获。

↖(^ω^)↗加油ing!