有3个数据库:CUSTOM 字段:custom_id, product_id
PRODUCT 字段:product_id, product_name,prod_number,
PRICE 字段:price, custom_id, prod_number,priority现在我要3个数据库中关联选择出几个字段(custom_id, product_id,price)放入另外一张表data中,但是每次执行选择后(见下面语句)可能会出现两条数据,这两条数据中除了custom不同外其他两个字段相同,但事实上我只要其中一条,就是根据PRICE表中的priority看那个custom_id的priority(优先级)高就选哪条,这个我不知如何实现。
下面是我能做的,不过我觉得有问题:
select into data custom_id, product_id,price
from custom a, product b, price c
where
a.custom_id = c.product_id
and a.product_id = b.product_id
//and ......(如何实现之选优先级高一条)
and rownum<2
由于我在后面加了rownum<2,所以一开始决定先在//and...中先按priority先order by一下就可以了,但是根本没这么简单。哪个高手帮我看看如何实现,谢谢!
from (select a.custom_id,
a.product_id,
c.price,
row_number() over(partition by a.product_id,c.price order by c.priority desc) rn
from custom a, product b, price c
where a.custom_id = c.custom_id
and a.product_id = b.product_id)
where rn = 1;
insert into data custom_id, product_id,price
(select a.custom_id,a.product_id,c.price
from custom a, product b, price c
where a.product_id = b.product_id
and a.custom_id = c.custom_id
and c.custom_id in
(select custom_id ,max(priority)
from price group by custum_id))
最好把每个表的主键列出来,好知道那个是1:n的关系。
from custom a inner join product b on(a.product_id = b.product_id) inner join price c on (a.custom_id in (select custom_id from price order by priority desc))
就是同一个product_id对应了两个不同的custom_id,猜想的.但是根据你描述的,应该是这样的 .
a.custom_id = c.product_id ( ?是不是a.custom_id = c.custom_id )
and a.product_id = b.product_id修改后依旧的话,检查一下CUSTOM中同一个product_id是否对应了同一个custom_id