lsa_YOS/src/custom/restcontroller/sale/salestarget/SQL/企业-订单.sql

with sa_salestarget as (
      select point,year,sys_enterpriseid,sum(amount) amount from (SELECT  0+CAST(if(isclose,t1.price*t1.deliedqty,amount) AS CHAR) amount, MONTH(t2.billdate) point,YEAR(t2.billdate) year,t2.sys_enterpriseid FROM sa_orderitems t1
    INNER JOIN sa_order t2 ON t1.sa_orderid=t2.sa_orderid AND t1.siteid=t2.siteid
WHERE t1.siteid = $siteid$ and t2.sys_enterpriseid in $sys_enterpriseid$ and $where1$ and YEAR(t2.billdate)=$year$ ) a
GROUP BY point,year,sys_enterpriseid
    )
SELECT DISTINCT m.year,
                m.sys_enterpriseid,
                ifnull(m1.amount,0)+ ifnull(m2.amount,0)+ifnull(m3.amount,0)+ ifnull(m4.amount,0)+ ifnull(m5.amount,0)+  ifnull(m6.amount,0)+ ifnull(m7.amount,0)+ ifnull(m8.amount,0)+  ifnull(m9.amount,0) + ifnull(m10.amount,0)+ ifnull(m11.amount,0)+  ifnull(m12.amount,0)  as y1a,
                ifnull(m1.amount,0)+ ifnull(m2.amount,0)+ifnull(m3.amount,0)  as s1a,
                ifnull(m4.amount,0)+ ifnull(m5.amount,0)+ifnull(m6.amount,0)  as s2a,
                ifnull(m7.amount,0)+ ifnull(m8.amount,0)+ifnull(m9.amount,0)  as s3a,
                ifnull(m10.amount,0)+ ifnull(m11.amount,0)+ifnull(m12.amount,0) as s4a,
                ifnull(m1.amount,0) as m1a,
                ifnull(m2.amount,0) as m2a,
                ifnull(m3.amount,0) as m3a,
                ifnull(m4.amount,0) as m4a,
                ifnull(m5.amount,0) as m5a,
                ifnull(m6.amount,0) as m6a,
                ifnull(m7.amount,0) as m7a,
                ifnull(m8.amount,0) as m8a,
                ifnull(m9.amount,0) as m9a,
                ifnull(m10.amount,0) as m10a,
                ifnull(m11.amount,0) as m11a,
                ifnull(m12.amount,0) as m12a
from sa_salestarget m
         LEFT JOIN sa_salestarget m1 ON m1.year = m.year and m1.point = 1 and m.sys_enterpriseid = m1.sys_enterpriseid
         LEFT JOIN sa_salestarget m2 ON m2.year = m.year and m2.point = 2 and m.sys_enterpriseid = m2.sys_enterpriseid
         LEFT JOIN sa_salestarget m3 ON m3.year = m.year and m3.point = 3 and m.sys_enterpriseid = m3.sys_enterpriseid
         LEFT JOIN sa_salestarget m4 ON m4.year = m.year and m4.point = 4 and m.sys_enterpriseid = m4.sys_enterpriseid
         LEFT JOIN sa_salestarget m5 ON m5.year = m.year and m5.point = 5 and m.sys_enterpriseid = m5.sys_enterpriseid
         LEFT JOIN sa_salestarget m6 ON m6.year = m.year and m6.point = 6 and m.sys_enterpriseid = m6.sys_enterpriseid
         LEFT JOIN sa_salestarget m7 ON m7.year = m.year and m7.point = 7 and m.sys_enterpriseid = m7.sys_enterpriseid
         LEFT JOIN sa_salestarget m8 ON m8.year = m.year and m8.point = 8 and m.sys_enterpriseid = m8.sys_enterpriseid
         LEFT JOIN sa_salestarget m9 ON m9.year = m.year and m9.point = 9 and m.sys_enterpriseid = m9.sys_enterpriseid
         LEFT JOIN sa_salestarget m10 ON m10.year = m.year and m10.point = 10 and m.sys_enterpriseid = m10.sys_enterpriseid
         LEFT JOIN sa_salestarget m11 ON m11.year = m.year and m11.point = 11 and m.sys_enterpriseid = m11.sys_enterpriseid
         LEFT JOIN sa_salestarget m12 ON m12.year = m.year and m12.point = 12 and m.sys_enterpriseid = m12.sys_enterpriseid
ORDER BY m.year desc