问题有点复杂,听我慢慢描述,有个一表, 结构如下:supplier_id,product_id,related_product_id1,related_product_id2,related_product_id3表中本来有supplier_id 和product_id ,一个supplier 有至少一个product要求,给每个product 找三个related products. 当然也可能少三个,如果这个supplier 只有少于3个products 的话。
related product 必须属于同一个supplier的。现在做法是 :UPDATE product_ranking pfr SET pfr.related_product_id1=
( SELECT p0.product_id FROM product_ranking p0
WHERE p0.supplier_id=pfr.supplier_id AND p0.product_id!=pfr.product_id AND rownum=1) UPDATE product_ranking pfr SET pfr.related_product_id2=
( SELECT p0.product_id FROM product_ranking p0
WHERE p0.supplier_id=pfr.supplier_id AND p0.product_id!=pfr.product_id
AND p0.product_id!=pfr.related_product_id1 AND rownum=1) UPDATE product_ranking pfr SET pfr.related_product_id3=
( SELECT p0.product_id FROM product_ranking p0
WHERE p0.supplier_id=pfr.supplier_id AND p0.product_id!=pfr.product_id
AND p0.product_id!=pfr.related_product_id1 AND p0.product_id!=pfr.related_product_id2 AND rownum=1) 这样做的问题是,对于同一个supplier 不同product 基本上都是同样的related 的products, 如下绿色标志数据:
"SUPPLIER_ID","PRODUCT_ID","RELATED_PRODUCT_ID1","RELATED_PRODUCT_ID2","RELATED_PRODUCT_ID3"
6007000000732,8804315839,8817094314,8817094587,8807871093
6007000000732,8807871093,8817094314,8817094587,8817094306
6007000000732,8817094306,8817094314,8817094587,8807871093
6007000000732,8817094314,8817094587,8807871093,8817094306
6007000000732,8817094587,8817094314,8807871093,8817094306怎么才能让3个related product 循环进行呢?
related product 必须属于同一个supplier的。现在做法是 :UPDATE product_ranking pfr SET pfr.related_product_id1=
( SELECT p0.product_id FROM product_ranking p0
WHERE p0.supplier_id=pfr.supplier_id AND p0.product_id!=pfr.product_id AND rownum=1) UPDATE product_ranking pfr SET pfr.related_product_id2=
( SELECT p0.product_id FROM product_ranking p0
WHERE p0.supplier_id=pfr.supplier_id AND p0.product_id!=pfr.product_id
AND p0.product_id!=pfr.related_product_id1 AND rownum=1) UPDATE product_ranking pfr SET pfr.related_product_id3=
( SELECT p0.product_id FROM product_ranking p0
WHERE p0.supplier_id=pfr.supplier_id AND p0.product_id!=pfr.product_id
AND p0.product_id!=pfr.related_product_id1 AND p0.product_id!=pfr.related_product_id2 AND rownum=1) 这样做的问题是,对于同一个supplier 不同product 基本上都是同样的related 的products, 如下绿色标志数据:
"SUPPLIER_ID","PRODUCT_ID","RELATED_PRODUCT_ID1","RELATED_PRODUCT_ID2","RELATED_PRODUCT_ID3"
6007000000732,8804315839,8817094314,8817094587,8807871093
6007000000732,8807871093,8817094314,8817094587,8817094306
6007000000732,8817094306,8817094314,8817094587,8807871093
6007000000732,8817094314,8817094587,8807871093,8817094306
6007000000732,8817094587,8817094314,8807871093,8817094306怎么才能让3个related product 循环进行呢?
另外,你原表是不是只有supplier_id 和product_id这两个字段?而现在是要多三个related products id 是这样的嘛?
在你内层查询中先加个随机排序后再取呢?order by dbms_random.value()
如:UPDATE product_ranking pfr SET pfr.related_product_id1=
SELECT p0.product_id FROM (
SELECT p0.supplier_id,p0.product_id,ROWNUM rn FROM product_ranking p0 order by dbms_random.value() ) p0
WHERE p0.supplier_id=pfr.supplier_id AND p0.product_id!=pfr.product_id and rn=1; 只要能保证相同supplier_id下不同product_id就行
不过你原来的SQL就是可以的,只是你说要同一个supplier的product的related products 尽量不一样
你这个怎么来保证尽量不一样呢?
假设同一个supplier的product只有三个,那么此三个product的related products 就肯定是一样的
我是想做到随机,像你说的那样,或者循环。
如果能随机最好。问题我找不出你写的那个sql有什么错,但是就是没有update :(
改成:
UPDATE product_ranking pfr SET pfr.related_product_id1=
SELECT p0.product_id FROM (
SELECT * FROM product_ranking p0 order by dbms_random.value() ) p0
WHERE p0.supplier_id=pfr.supplier_id AND p0.product_id!=pfr.product_id and rownum=1; 就好了.感谢gelyon:P