lsa_YOS/src/custom/restcontroller/sale/itemgroup/SQL/商品组商品明细查询.sql

SELECT t1.sa_itemgroupmxid,
       t1.sequence,
       t2.*,
       t6.unitname,
       ifnull(t3.candispatchqty, 0)   candispatchqty,
       ifnull(t3.cansaleqty, 0)       cansaleqty,
       ifnull(t3.qty, 0)              totalaty,
       (SELECT (case
                    when t3.cansaleqty >= t2.stockstatus1 then '充足'
                    when t3.cansaleqty <= t2.stockstatus2 then '缺货'
                    else '紧缺' end)) stockstatus,
       t5.brandname
FROM sa_itemgroupmx t1
         inner JOIN plm_item t2 ON t1.itemid = t2.itemid and  ifnull(t2.isonsale,0)=1
    AND t1.siteid = t2.siteid
         LEFT JOIN plm_unit t6 ON t6.unitid = t2.unitid
    AND t6.siteid = t2.siteid
         LEFT JOIN st_invbal_sale t3 ON t2.siteid = t3.siteid AND t2.itemid = t3.itemid
         inner join sa_itemgroup t4 on t1.siteid = t4.siteid and t1.sa_itemgroupid = t4.sa_itemgroupid
         left join sa_brand t5 on t4.siteid = t5.siteid and t4.sa_brandid = t5.sa_brandid
where $where$
  and t1.sa_itemgroupid = $sa_itemgroupid$
  and t1.siteid = $siteid$