CUCU2/src/custom/restcontroller/sale/itemgroup/SQL/查询授权范围内的商品组商品.sql

SELECT t1.sa_itemgroupid,max(if(t1.siteid='ccyosg',(if(ifnull(t8.price,0)!=0 && t8.price<=t7.price,t8.price,t7.price)),t7.price)) maxprice,min(if(t1.siteid='ccyosg',(if(ifnull(t8.price,0)!=0 && t8.price<=t7.price,t8.price,t7.price)),t7.price)) minprice
FROM sa_itemgroupmx t1
         LEFT JOIN plm_item t2 ON t1.itemno = t2.itemno
    AND t1.siteid = t2.siteid
         LEFT JOIN sa_itemsaleclass t4 ON t2.itemid = t4.itemid
    AND t2.siteid = t4.siteid
         LEFT JOIN plm_itemclass t5 ON t5.itemclassid = t4.itemclassid
    AND t5.siteid = t4.siteid
         LEFT JOIN plm_unit t6 ON t6.unitid = t2.unitid
    AND t6.siteid = t2.siteid
         left join sa_itemprice t7 on t2.itemid = t7.itemid and t7.pricegrade = $pricegrade$
         left join sa_itemprice t8 on t2.itemid = t8.itemid and t8.pricegrade =6
where t5.itemclassid in $itemclassid$ and t1.siteid=$siteid$ and ifnull(t2.isonsale,0)=1
group by t1.sa_itemgroupid