select
a.pk_group as pkgroup,
a.pk_org as pkorg,
a1.name as ename,
dept.name as dept,
person.name as person,
customer.name as customer,
material.name as material,
mm.name as mm,
b.vfree1 as b,
sum(case when substr(b.dbilldate ,1,4)=substr(parameter('stime'),1,4) then b.nnum else 0 end) as yearproduce,
sum(case when substr(b.dbilldate ,1,4)=substr(parameter('stime'),1,4) and substr(b.dbilldate ,6,2)=1 then b.nnum else 0 end) as oneproduce,
sum(case when substr(b.dbilldate ,1,4)=substr(parameter('stime'),1,4) and substr(b.dbilldate ,6,2)=2 then b.nnum else 0 end) as twoproduce,
sum(case when substr(b.dbilldate ,1,4)=substr(parameter('stime'),1,4) and substr(b.dbilldate ,6,2)=3 then b.nnum else 0 end) as threeproduce,
sum(case when substr(b.dbilldate ,1,4)=substr(parameter('stime'),1,4) and substr(b.dbilldate ,6,2)=4 then b.nnum else 0 end) as fourproduce,
sum(case when substr(b.dbilldate ,1,4)=substr(parameter('stime'),1,4) and substr(b.dbilldate ,6,2)=5 then b.nnum else 0 end) as fiveproduce,
sum(case when substr(b.dbilldate ,1,4)=substr(parameter('stime'),1,4) and substr(b.dbilldate ,6,2)=6 then b.nnum else 0 end) as sixproduce,
sum(case when substr(b.dbilldate ,1,4)=substr(parameter('stime'),1,4) and substr(b.dbilldate ,6,2)=7 then b.nnum else 0 end) as sevenproduce,
sum(case when substr(b.dbilldate ,1,4)=substr(parameter('stime'),1,4) and substr(b.dbilldate ,6,2)=8 then b.nnum else 0 end) as eightproduce,
sum(case when substr(b.dbilldate ,1,4)=substr(parameter('stime'),1,4) and substr(b.dbilldate ,6,2)=9 then b.nnum else 0 end) as nineproduce,
sum(case when substr(b.dbilldate ,1,4)=substr(parameter('stime'),1,4) and substr(b.dbilldate ,6,2)=10 then b.nnum else 0 end) as tenproduce,
sum(case when substr(b.dbilldate ,1,4)=substr(parameter('stime'),1,4) and substr(b.dbilldate ,6,2)=11 then b.nnum else 0 end) as elevenproduce,
sum(case when substr(b.dbilldate ,1,4)=substr(parameter('stime'),1,4) and substr(b.dbilldate ,6,2)=12 then b.nnum else 0 end) as twentyproduce,
twomaterial.name as two,
threematerial.name as three,
fourmaterial.name as four
from so_saleorder a
left join so_saleorder_b b
on a.csaleorderid = b.csaleorderid
left join org_salesorg a1
on a.pk_org = a1.pk_salesorg
left join org_dept dept
on a.cdeptid = dept.pk_dept
left join bd_psndoc person
on a.cemployeeid = person.pk_psndoc
left join bd_customer customer
on a.ccustomerid = customer.pk_customer
left join bd_material material
on B.cmaterialid = material.pk_material
left join bd_measdoc mm
on b.cunitid = mm.pk_measdoc
left join bd_material wu
on b.cmaterialvid=wu.pk_material
left join bd_marbasclass marbasclass
on wu.pk_marbasclass = marbasclass.pk_marbasclass
left join bd_marbasclass twomaterial
on marbasclass.pk_parent=twomaterial.pk_marbasclass
left join bd_marbasclass threematerial
on twomaterial.pk_parent=threematerial.pk_marbasclass
left join bd_marbasclass fourmaterial
on threematerial.pk_parent=fourmaterial.pk_marbasclass
group by a1.name,
dept.name,
person.name,
customer.name,
material.name,
mm.name,
b.vfree1,
twomaterial.name,
threematerial.name,
fourmaterial.name,
a.pk_group,
a.pk_org
左关联,case when,每月的销量select a.pk_group as pkgroup, a.pk_org as pkorg, a1.name as ename, dept.name as dept, person.name as person, customer.name as customer, material.name as material, mm.name as mm, b.vfree1 as