今天写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!