我先把我想要做的,大致语句写出来!
INSERT INTO e_consprc_snap
(a, b, c)
SELECT a, b,
(SELECT prc_tactic_snap_id
form (SELECT prc_tactic_snap_id
FROM e_consprc_tactic_snap b
WHERE b.org_no = in_r_plan.org_no
AND b.sp_id = a.sp_id
AND b.calc_id = c.calc_id
ORDER BY b.rela_app_no ASC)
WHERE rownum = 1) c
FROM c_bf_consprc a, e_cons_snap c
WHERE ..就是我在里面的b表,好像不可以用到 a,c表.
我想把b表的一些记录排序后,找到最小的一项插入到新表,高手们怎么解决阿?
INSERT INTO e_consprc_snap
(a, b, c)
SELECT a, b,
(SELECT prc_tactic_snap_id
form (SELECT prc_tactic_snap_id
FROM e_consprc_tactic_snap b
WHERE b.org_no = in_r_plan.org_no
AND b.sp_id = a.sp_id
AND b.calc_id = c.calc_id
ORDER BY b.rela_app_no ASC)
WHERE rownum = 1) c
FROM c_bf_consprc a, e_cons_snap c
WHERE ..就是我在里面的b表,好像不可以用到 a,c表.
我想把b表的一些记录排序后,找到最小的一项插入到新表,高手们怎么解决阿?
from A,B
where a.XX=b.XXX
group by a,b
having B.id=min(B.id)你自己试一试吧,不知道你的表之间的关系,我认为使用Having子句应该可以解决纳米的问题。
select a,b,c
from (
select a,b,b.prc_tactic_snap_id c,row_number()over(partition by a,b order by b.rela_app_no ASC ) rn
from c_bf_consprc a, e_cons_snap c ,e_consprc_tactic_snap b
WHERE ..
and b.org_no(+) = in_r_plan.org_no
AND b.sp_id(+) = a.sp_id
AND b.calc_id(+) = c.calc_id )
where rn=1
;
INSERT INTO e_consprc_snap
(a, b, c)
SELECT a, b,
(SELECT min(prc_tactic_snap_id )
FROM e_consprc_tactic_snap b
WHERE b.org_no = in_r_plan.org_no
AND b.sp_id = a.sp_id
AND b.calc_id = c.calc_id
) c
FROM c_bf_consprc a, e_cons_snap c
WHERE ..
你这种写法效率很低,建议用我上面的写法,3个表关联,绝对比子查询做字段效率高。
SELECT a, b,
(SELECT prc_tactic_snap_id
from (SELECT prc_tactic_snap_id
FROM e_consprc_tactic_snap b
WHERE b.org_no = in_r_plan.org_no
AND exists(select 1 from c_bf_consprc a, e_cons_snap c where b.sp_id = a.sp_id
AND b.calc_id = c.calc_id )
ORDER BY b.rela_app_no ASC)
WHERE rownum = 1) c
FROM c_bf_consprc a, e_cons_snap c
WHERE ..
下面写了个,没有数据可以test下,感觉这个方法是可以批量插入数据的,希望对楼主有帮助。
SELECT a, b, c
from (SELECT prc_tactic_snap_id
from (SELECT prc_tactic_snap_id,
row_num() over(partition by a.sp_id, c.calc_id, b.org_no order by b.rela_app_no ASC) rn
FROM e_consprc_tactic_snap b, c_bf_consprc a, e_cons_snap c
WHERE b.org_no = in_r_plan.org_no
AND b.sp_id = a.sp_id
AND b.calc_id = c.calc_id......) t
WHERE rn = 1)
SELECT a, b, c
from (SELECT prc_tactic_snap_id c
from (SELECT prc_tactic_snap_id,
row_num() over(partition by a.sp_id, c.calc_id, b.org_no order by b.rela_app_no ASC) rn
FROM e_consprc_tactic_snap b, c_bf_consprc a, e_cons_snap c
WHERE b.org_no = in_r_plan.org_no
AND b.sp_id = a.sp_id
AND b.calc_id = c.calc_id......) t
WHERE rn = 1) tb
from (SELECT prc_tactic_snap_id c
from (SELECT prc_tactic_snap_id,
row_number() over(partition by a.sp_id, c.calc_id, b.org_no order by b.rela_app_no ASC) rn
FROM e_consprc_tactic_snap b, c_bf_consprc a, e_cons_snap c
WHERE b.org_no = in_r_plan.org_no
AND b.sp_id = a.sp_id
AND b.calc_id = c.calc_id......) t
WHERE rn = 1) tb
你不试下怎么知道我写的sql不对呢,绝对不是一条记录,你不妨试试,因为你的思路虽然好,但是写法效率太低了。我的代码改写成下面这种可能思路清晰一点。
select a,b,c
from (
select a,b,b.prc_tactic_snap_id c,row_number()over(partition by a,b order by b.rela_app_no ASC ) rn
from c_bf_consprc a join e_cons_snap c on ....
left join e_consprc_tactic_snap b on b.sp_id = a.sp_id and b.calc_id = c.calc_id and b.org_no = in_r_plan.org_no
WHERE ..
)
where rn=1
;