cucu_YOS/src/custom/restcontroller/webmanage/sale/order/SQL/促销方案商品列表.sql

SELECT t1.itemid,
       t1.itemno,
       t1.itemname,
       t1.model,
       t1.batchcontrol,
       t1.orderminqty_auxunit,
       t3.orderminqty,
       t1.orderaddqty_auxunit,
       t3.orderaddqty,
       t3.groupqty,
       t1.spec,
       t1.marketprice,
       (SELECT unitname FROM plm_unit WHERE unitid = t1.unitid and siteid = t1.siteid)    unit,
       (SELECT unitname FROM plm_unit WHERE unitid = t2.auxunitid and siteid = t1.siteid) auxunit,
       t2.conversionrate,
       t5.oldprice,
       t5.pricegrade,
       t5.price
from plm_item t1
         LEFT JOIN plm_unitgroup t2 ON t2.unitgroupid = t1.unitgroupid and t2.siteid = t1.siteid
         LEFT JOIN sa_promotion_items t3 ON t3.itemid = t1.itemid and t3.siteid = t1.siteid
         LEFT JOIN sa_promotion_itemprice t5
                   ON t5.itemid = t3.itemid and t5.siteid = t3.siteid and t5.sa_promotionid = t3.sa_promotionid
         LEFT JOIN sys_enterprise t6 ON t6.grade = t5.pricegrade and t6.siteid = t5.siteid
WHERE t1.siteid = $siteid$
  and t1.status = '审核'
  and t3.sa_promotionid = $sa_promotionid$
  and t6.sys_enterpriseid = $sys_enterpriseid$
  and $where$