ora左关联查询

左关联，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 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