UPDATE i_edi_order o
SET pricestd =
(SELECT max(pp.pricestd)
FROM m_productprice pp, m_pricelist_version pv,m_pricelist pl
WHERE pp.m_pricelist_version_id = pv.m_pricelist_version_id
AND pv.m_pricelist_id = o.m_pricelist_id
AND pp.m_product_id = o.m_product_id)
WHERE NOT EXISTS (
SELECT *
FROM m_productprice pp, m_pricelist_version pv
WHERE pp.m_pricelist_version_id = pv.m_pricelist_version_id
AND pv.m_pricelist_id = o.m_pricelist_id
AND pp.pricelist = o.priceactual
AND pp.m_product_id = o.m_product_id
AND o.m_product_id IS NOT NULL)
and E_Imp_Exe_ID=1004106把以上的SQL能简化一下么???
SET pricestd =
(SELECT max(pp.pricestd)
FROM m_productprice pp, m_pricelist_version pv,m_pricelist pl
WHERE pp.m_pricelist_version_id = pv.m_pricelist_version_id
AND pv.m_pricelist_id = o.m_pricelist_id
AND pp.m_product_id = o.m_product_id)
WHERE NOT EXISTS (
SELECT *
FROM m_productprice pp, m_pricelist_version pv
WHERE pp.m_pricelist_version_id = pv.m_pricelist_version_id
AND pv.m_pricelist_id = o.m_pricelist_id
AND pp.pricelist = o.priceactual
AND pp.m_product_id = o.m_product_id
AND o.m_product_id IS NOT NULL)
and E_Imp_Exe_ID=1004106把以上的SQL能简化一下么???
估计楼下的知道怎么简化了
oracle本身也必须要求这样写,想简单的话,sqlserver和mysql倒是可以简单些来写
表 m_pricelist_version,PK:m_pricelist_version_id,FK:m_pricelist_id
表 m_productprice,PK:m_pricelist_version_id,m_product_id
表 i_edi_order,PK:i_edi_order_id
1.根据m_pricelist_id,m_product_id找出i_edi_order.priceactual和m_productprice.pricelist不同的i_edi_order记录
2.修改1中找出的i_edi_order记录中的i_edi_order.pricestd的值,该设定的值需要根据m_pricelist_id,m_product_id找出m_productprice.pricestd最大的值
将该值设置到1中找出的i_edi_order记录。
注:要一条SQL搞定。
你不就是一条sql搞定的吗?想要一条sql又简单,是不太现实的,你的这个sql可以做一下等价变换,例如把not exsist 转换成not in,left join的写法
但是还是很麻烦,很多时候是不需要一条sql搞定的,怎么样处理最快,最合理才是最重要的
SET pricestd =
(SELECT pp.pricestd
FROM m_productprice pp, m_pricelist_version pv,m_pricelist pl
WHERE pp.m_pricelist_version_id = pv.m_pricelist_version_id
AND pv.m_pricelist_id = o.m_pricelist_id
AND pp.m_product_id = o.m_product_id order by pp.pricestd desc where rownum = 1)
WHERE E_Imp_Exe_ID=1004106 AND NOT EXISTS (
SELECT 1
FROM m_productprice pp, m_pricelist_version pv
WHERE pp.m_pricelist_version_id = pv.m_pricelist_version_id
AND pv.m_pricelist_id = o.m_pricelist_id
AND pp.pricelist = o.priceactual
AND pp.m_product_id = o.m_product_id
AND o.m_product_id IS NOT NULL)效果可能回好点,在pp.pricestd建一个索引
SET pricestd =
(SELECT pp.pricestd
FROM m_productprice pp, m_pricelist_version pv,m_pricelist pl
WHERE pp.m_pricelist_version_id = pv.m_pricelist_version_id
AND pv.m_pricelist_id = o.m_pricelist_id
AND pp.m_product_id = o.m_product_id and rownum = 1 order by pp.pricestd desc)
WHERE E_Imp_Exe_ID=1004106 AND NOT EXISTS (
SELECT 1
FROM m_productprice pp, m_pricelist_version pv
WHERE pp.m_pricelist_version_id = pv.m_pricelist_version_id
AND pv.m_pricelist_id = o.m_pricelist_id
AND pp.pricelist = o.priceactual
AND pp.m_product_id = o.m_product_id
AND o.m_product_id IS NOT NULL) 效果可能回好点,在pp.pricestd建一个索引