liusong_china进。。。问题请教···

[xjswuai]

现在的sql是这样的
select a.so_nbr,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
结果是这样


但是要求是
-------------------
so_nbr col1 col2
200153000516 100501 1
200153000516 100501 1
200153000515 100516 1
200153000515 100516 1


麻烦 liusong_china 给看看谢谢 ！

[liusong_china]

把你的查询结果存到表tb2里，作了一个例子。。。。

10:51:03 scott@TUNGKONG> select * from tb2;

SO_NBR                       ID
-------------------- ----------
200153000516             100501
200153000516                  1
200153000515             100516
200153000515                  1

已用时间:  00: 00: 00.00
10:51:08 scott@TUNGKONG> select so_nbr,max(id) over(partition by so_nbr) col1,min(id) over(partition by so_nbr) col2 from tb2
10:51:11   2  /

SO_NBR                     COL1       COL2
-------------------- ---------- ----------
200153000515             100516          1
200153000515             100516          1
200153000516             100501          1
200153000516             100501          1

已用时间:  00: 00: 00.01

[cosio]

with temp as
(
select '200153000516' a, 100501 b from dual
union all
select '200153000516', 1 from dual
union all
select '200153000515', 100516 from dual
union all
select '200153000515', 1 from dual
)
select a,decode(b,1,rn,b)
from
(
select a,b,lag(b)over(partition by a order by a) rn from temp
)

[liusong_china]

把你的id字段替换成max(id) over(partition by so_nbr) col1,min(id) over(partition by so_nbr) col2。。

用一下分析函数，这样就拆成两个字段了。。。。

[liusong_china]

10:51:03 scott@TUNGKONG> select * from tb2;

SO_NBR                       ID
-------------------- ----------
200153000516             100501
200153000516                  1
200153000515             100516
200153000515                  1

已用时间:  00: 00: 00.00
10:51:08 scott@TUNGKONG> select so_nbr,max(id) over(partition by so_nbr) col1,min(id) over(partition by so_nbr) col2 from tb2
10:51:11   2  /

SO_NBR                     COL1       COL2
-------------------- ---------- ----------
200153000515             100516          1
200153000515             100516          1
200153000516             100501          1
200153000516             100501          1

已用时间:  00: 00: 00.01

[liusong_china]

。。。。

[xjswuai]

正在执行中......

Executing.....

[liusong_china]

[Quote=引用 6 楼 xjswuai 的回复:]
正在执行中......

Executing.....
[/Quote]

楼主你确定你抽取的测试数据能代表你的想实现的逻辑吗。。。。。。

譬如说：同一个so_nbr是不是只有这两条记录等等。。。