| 123456789101112131415161718192021 |
- with sa_salestarget as (
- select 0+CAST(sum(amount) AS CHAR) amount,point,sys_enterpriseid,year from(SELECT (t5.qty*t1.price) amount, QUARTER(t6.checkdate) point,ifnull(t6.sys_enterpriseid,0) sys_enterpriseid,YEAR(t6.checkdate) year
- FROM sa_orderitems t1
- inner join st_stockbill_items t5 ON t5.sa_orderitemsid = t1.sa_orderitemsid AND t5.siteid = t1.siteid
- inner join st_stockbill t6 ON t6.st_stockbillid = t5.st_stockbillid AND t6.siteid = t5.siteid
- WHERE YEAR (t6.checkdate)=$year$ and t2.sys_enterpriseid in $sys_enterpriseid$ AND t1.siteid=$siteid$ and t6.type='销售出库' and t6.rb=1) a
- GROUP BY point,sys_enterpriseid,year
- )
- SELECT DISTINCT m.year,
- m.sys_enterpriseid,
- ROUND((ifnull(m1.amount,0)+ ifnull(m2.amount,0)+ifnull(m3.amount,0)+ ifnull(m4.amount,0))/10000,$num$) as y1a,
- ROUND((ifnull(m1.amount,0))/10000,$num$) as s1a,
- ROUND((ifnull(m2.amount,0))/10000,$num$) as s2a,
- ROUND((ifnull(m3.amount,0))/10000,$num$) as s3a,
- ROUND((ifnull(m4.amount,0))/10000,$num$) as s4a
- 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
- ORDER BY m.year desc
|
