首先说下表结构.
主表:table_main
中间表a:temp_a
中间表b:temp_b
其中主表的so_nbr属于联合主键。为了省事,另一个主键没有写。
先说明下我要描述的意思。
--------------------------------
so_nbr so_charge_type_id
200911151853 505
200911151853 88885
200911151854 501
200911151854 99998
200911151854 99997
200911151856 502
200911151856 99996
200911170857 601
200911170857 501
200911170857 99998
200911170857 66667以这两条记录为例,我要把so_charge_type_id的505与88885分别拿出来去中间表取出我需要的.
中间表a:
--------------------------------
so_charge_type_id charge_id
505 100505
501 100501
601 100601这个charge_id就是我想要的,通过主表中的505来获得这里的charge_id中间表b:
--------------------------------
so_charge_type_id newid
88885 1
99998 2
99997 3
66667 5这个newid也是我想要的,通过主表中的88885来获得这里的newid
最终结果:
--------------------------------
so_nbr charge_id newid
200911151854 100501 2
200911151854 100501 3
200911151853 100505 1
200911151856 100502
200911170857 100601 5
200911170857 100501 2在看主表中so_nbr id为200911151854的一组数据。其中so_charge_type_id里的99998,99997到中间表去关联的话会关联出
两个newid,而charge_id只能通过501关联出一个来。这样就得通过501关联出来的100501分别对应上99998与99997关联出来的newid,2和3,也就是空出的地方用同一组的charge_id来补上。而newid如果在中间表取不到可以为空,而不能用charge_id或同一组的newid来替代,例如最终结果里的最后一条那样。还有一个特殊要求就是,如果主表里某一组so_charge_type_id中有601或602的那就要取出同组以6开头的5位长的值,然后关联中间表B获得newid麻烦liusong_china了!
主表:table_main
中间表a:temp_a
中间表b:temp_b
其中主表的so_nbr属于联合主键。为了省事,另一个主键没有写。
先说明下我要描述的意思。
--------------------------------
so_nbr so_charge_type_id
200911151853 505
200911151853 88885
200911151854 501
200911151854 99998
200911151854 99997
200911151856 502
200911151856 99996
200911170857 601
200911170857 501
200911170857 99998
200911170857 66667以这两条记录为例,我要把so_charge_type_id的505与88885分别拿出来去中间表取出我需要的.
中间表a:
--------------------------------
so_charge_type_id charge_id
505 100505
501 100501
601 100601这个charge_id就是我想要的,通过主表中的505来获得这里的charge_id中间表b:
--------------------------------
so_charge_type_id newid
88885 1
99998 2
99997 3
66667 5这个newid也是我想要的,通过主表中的88885来获得这里的newid
最终结果:
--------------------------------
so_nbr charge_id newid
200911151854 100501 2
200911151854 100501 3
200911151853 100505 1
200911151856 100502
200911170857 100601 5
200911170857 100501 2在看主表中so_nbr id为200911151854的一组数据。其中so_charge_type_id里的99998,99997到中间表去关联的话会关联出
两个newid,而charge_id只能通过501关联出一个来。这样就得通过501关联出来的100501分别对应上99998与99997关联出来的newid,2和3,也就是空出的地方用同一组的charge_id来补上。而newid如果在中间表取不到可以为空,而不能用charge_id或同一组的newid来替代,例如最终结果里的最后一条那样。还有一个特殊要求就是,如果主表里某一组so_charge_type_id中有601或602的那就要取出同组以6开头的5位长的值,然后关联中间表B获得newid麻烦liusong_china了!
-------------------- -----
200911151853 505
200911151853 88885
200911151854 501
200911151854 99998
200911151854 99997
200911151856 502
200911151856 99996
200911170857 601
200911170857 501
200911170857 99998
200911170857 66667已选择11行。已用时间: 00: 00: 00.00
10:22:39 scott@TUNGKONG> select * from temp_a;SO_CH CHARGE_ID
----- ----------
505 100505
501 100501
601 100601
502 100502已用时间: 00: 00: 00.01
10:22:44 scott@TUNGKONG> select * from temp_b;SO_CH NEWID
----- ----------
88885 1
99998 2
99997 3
66667 5已用时间: 00: 00: 00.01
10:22:50 scott@TUNGKONG> select a.so_nbr,charge_id,newid from
10:22:59 2 (select so_nbr,charge_id from table_main a,temp_a b where a.so_charge_type_id = b.so_charge_type_id and length(a.so_charge_type_id) = 3 and a.so_charge_type_id not in ('601','602')) a,
10:22:59 3 (select so_nbr,newid from table_main a,temp_b b where a.so_charge_type_id = b.so_charge_type_id and length(a.so_charge_type_id) = 5 and a.so_charge_type_id not like '6%') b
10:22:59 4 where a.so_nbr = b.so_nbr(+)
10:22:59 5 union all
10:22:59 6 select a.so_nbr,charge_id,newid from
10:22:59 7 (select so_nbr,charge_id from table_main a,temp_a b where a.so_charge_type_id = b.so_charge_type_id and length(a.so_charge_type_id) = 3 and a.so_charge_type_id in ('601','602')) a,
10:22:59 8 (select so_nbr,newid from table_main a,temp_b b where a.so_charge_type_id = b.so_charge_type_id and length(a.so_charge_type_id) = 5 and a.so_charge_type_id like '6%') b
10:22:59 9 where a.so_nbr = b.so_nbr(+)
10:22:59 10 order by 1;SO_NBR CHARGE_ID NEWID
-------------------- ---------- ----------
200911151853 100505 1
200911151854 100501 2
200911151854 100501 3
200911151856 100502
200911170857 100501 2
200911170857 100601 5已选择6行。已用时间: 00: 00: 00.01
where a.so_nbr = b.so_nbr(+)那后面的(+) 是什么意思?
麻烦了
10:22:59 2 (select so_nbr,charge_id from table_main a,temp_a b where a.so_charge_type_id = b.so_charge_type_id and length(a.so_charge_type_id) = 3 and a.so_charge_type_id not in ('601','602')) a,
10:22:59 3 (select so_nbr,newid from table_main a,temp_b b where a.so_charge_type_id = b.so_charge_type_id and length(a.so_charge_type_id) = 5 and a.so_charge_type_id not like '6%') b
10:22:59 4 where a.so_nbr = b.so_nbr(+)
10:22:59 5 union all
10:22:59 6 select a.so_nbr,charge_id,newid from
10:22:59 7 (select so_nbr,charge_id from table_main a,temp_a b where a.so_charge_type_id = b.so_charge_type_id and length(a.so_charge_type_id) = 3 and a.so_charge_type_id in ('601','602')) a,
10:22:59 8 (select so_nbr,newid from table_main a,temp_b b where a.so_charge_type_id = b.so_charge_type_id and length(a.so_charge_type_id) = 5 and a.so_charge_type_id like '6%') b
10:22:59 9 where a.so_nbr = b.so_nbr(+)
10:22:59 10 order by 1;
第10行是排序用的,按照so_nbr排序
第2行是主表table_main和临时表temp_a关联,得出charge_id字段;
第3行是主表table_main和临时表temp_b关联,得出newid字段;
然后这两个集合通过so_nbr关联,也就是第4行
再次谢谢你 liusong_china!