select so_nbr,seq,decode(length(a.so_charge_type_id),3,id) "charge_id",decode(length(a.so_charge_type_id),5,id) "payment_type_id"
from SO_CHARGE@db_dqyy a,
(
select to_number(b.so_charge_type_id) so_charge_type_id,to_number(b.newid) id from query_charge_to_boss b
union all
select so_charge_type_id,charge_id from so_charge_cat_id_to_boss c
) b
where a.so_charge_type_id = b.so_charge_type_id(+)
order by 1,2;
麻烦 liusong_china 给解释解释可以吗? 谢谢···感激不尽!!!
from SO_CHARGE@db_dqyy a,
(
select to_number(b.so_charge_type_id) so_charge_type_id,to_number(b.newid) id from query_charge_to_boss b
union all
select so_charge_type_id,charge_id from so_charge_cat_id_to_boss c
) b
where a.so_charge_type_id = b.so_charge_type_id(+)
order by 1,2;
麻烦 liusong_china 给解释解释可以吗? 谢谢···感激不尽!!!
select to_number(b.so_charge_type_id) so_charge_type_id,to_number(b.newid) id from query_charge_to_boss b
union all
select so_charge_type_id,charge_id from so_charge_cat_id_to_boss c
) b
这一段子查询是将你的两个中间表b,c组合成一个临时表,称之为b
这是a与b通过so_charge_type_id 字段做左连接。
左联时,会全显左表的数据,如果右表没有满足的条件的数据为null
---------
100501
1
1应该是把那个 1 与100501 并列显示出来能给看看吗 liusong_china?
结果应是
-----------------
chage_id payment_type_id
100501 1
1这样显示
中间表b表和c表会有so_charge_type_id相同的数据吗?
不会
b表的so_charge_type_id的长度一般都是5位 而c表的so_charge_type_id的长度是小于5位的只不过是把刚刚的payment_type_id的值往上提一下,-----------------
charge_id payment_id
100501
1
1改成
----------------------
charge_id payment_id
100501 1
1
这样,,,麻烦了·
你看呢?
decode(length(a.so_charge_type_id), 5, id) "payment_type_id"
from SO_CHARGE@db_dqyy a,
(select to_number(b.so_charge_type_id) so_charge_type_id,
to_number(newid) id
from query_charge_to_boss b
union all
select c.so_charge_type_id, c.charge_id from so_charge_cat_id_to_boss c) b
where a.so_charge_type_id = b.so_charge_type_id
group by a.so_nbr;
怎么会说表达式不对?
加了group by以后,decode的地方要加上max()函数的。。但是按so_nbr好像够呛能满足,不是很明白到底数据是怎么对应的试试吧