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

select t.* from (SELECT t1.promnum,
       t1.sa_promotionid,
       t1.promname,
       t1.remarks,
       t1.type,
       t1.sa_accountclassid,
       t1.sa_brandid,
       t1.tradefield,
       t2.brandname,
       t3.accountno,
       t3.accountname
FROM sa_promotion t1
         LEFT JOIN sa_brand t2 ON t1.sa_brandid = t2.sa_brandid
    AND t1.siteid = t2.siteid
         LEFT JOIN sa_accountclass t3 ON t1.sa_accountclassid = t3.sa_accountclassid
    AND t1.siteid = t3.siteid
where t1.siteid = $siteid$
  and t1.status = '发布'
  and t1.authmethod = 0
  and exists(select 1
             from sa_promotion_auth t2
             where t1.siteid = t2.siteid
               and t1.sa_promotionid = t2.sa_promotionid
               and t2.sys_enterpriseid = $sys_enterpriseid$)
union all
SELECT t1.promnum,
       t1.sa_promotionid,
       t1.promname,
       t1.remarks,
       t1.type,
       t1.sa_accountclassid,
       t1.sa_brandid,
       t1.tradefield,
       t2.brandname,
       t3.accountno,
       t3.accountname
FROM sa_promotion t1
         LEFT JOIN sa_brand t2 ON t1.sa_brandid = t2.sa_brandid
    AND t1.siteid = t2.siteid
         LEFT JOIN sa_accountclass t3 ON t1.sa_accountclassid = t3.sa_accountclassid
    AND t1.siteid = t3.siteid
where t1.siteid = $siteid$
  and t1.status = '发布'
  and t1.authmethod = 1
  and not exists(select 1
                 from sa_promotion_auth t2
                 where t1.siteid = t2.siteid
                   and t1.sa_promotionid = t2.sa_promotionid
                   and t2.sys_enterpriseid = $sys_enterpriseid$)) t where $where$