select NVL(sum(p.barsumcash),0),'JYRMB' AS FLAG from purchase p where p.fstatus ='采购' and p.materialtype = '机电'
and p.deliverydate between thisyear and thisdate and p.barmontype ='RMB'
union
select NVL(sum(p.barsumcash),0),'JYUSD' AS FLAG from purchase p where p.fstatus ='采购' and p.materialtype = '机电'
and p.deliverydate between thisyear and thisdate and p.barmontype ='USD'
union
select NVL(sum(p.barsumcash),0),'JYEUR' AS FLAG from purchase p where p.fstatus ='采购' and p.materialtype = '机电'
and p.deliverydate between thisyear and thisdate and p.barmontype ='EUR'
union------------------------------------------------------------------------------------------------
--机电当月信息
select NVL(sum(p.barsumcash),0),'JMRMB' AS FLAG from purchase p where p.fstatus ='采购' and p.materialtype = '机电'
and p.deliverydate between thismonth and thisdate and p.barmontype ='RMB'
union
select NVL(sum(p.barsumcash),0),'JMUSD' AS FLAG from purchase p where p.fstatus ='采购' and p.materialtype = '机电'
and p.deliverydate between thismonth and thisdate and p.barmontype ='USD'
union
select NVL(sum(p.barsumcash),0),'JMEUR' AS FLAG from purchase p where p.fstatus ='采购' and p.materialtype = '机电'
and p.deliverydate between thismonth and thisdate and p.barmontype ='EUR'
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
--材料全年信息
UNION
select NVL(sum(p.barsumcash),0),'CYRMB' AS FLAG from purchase p where p.fstatus ='采购' and p.materialtype = '材料'
and p.deliverydate between thisyear and thisdate and p.barmontype ='RMB'
union
select NVL(sum(p.barsumcash),0),'CYUSD' AS FLAG from purchase p where p.fstatus ='采购' and p.materialtype = '材料'
and p.deliverydate between thisyear and thisdate and p.barmontype ='USD'
union
select NVL(sum(p.barsumcash),0),'CYEUR' AS FLAG from purchase p where p.fstatus ='采购' and p.materialtype = '材料'
and p.deliverydate between thisyear and thisdate and p.barmontype ='EUR'
union
--材料当月信息
-----------------------------------------------------------------------------------------------------
select NVL(sum(p.barsumcash),0),'CMRMB' AS FLAG from purchase p where p.fstatus ='采购' and p.materialtype = '材料'
and p.deliverydate between thismonth and thisdate and p.barmontype ='RMB'
union
select NVL(sum(p.barsumcash),0),'CMUSD' AS FLAG from purchase p where p.fstatus ='采购' and p.materialtype = '材料'
and p.deliverydate between thismonth and thisdate and p.barmontype ='USD'
union
select NVL(sum(p.barsumcash),0),'CMEUR' AS FLAG from purchase p where p.fstatus ='采购' and p.materialtype = '材料'
and p.deliverydate between thismonth and thisdate and p.barmontype ='EUR';
这个代码其实只有 'CMEUR' AS FLAG 和 p.barmontype ='EUR'; 在变化,能不能写的简化一些?
你用decode
或者case when 的讲法,进行简化
这样只要一次扫描表就行了
decode(p.barmontype ,'RMB','JYRMB',
'USD','JYUSD'
'JYEUR')
AS FLAG
from purchase p where p.fstatus ='采购' and p.materialtype = '机电'
and p.deliverydate between thisyear and thisdate
and p.barmontype in('RMB','USD','EUR')
group by p.barmontypeunion后面的语句都可以这样改
Decode(Materialtype, '材料', 'C', '机电', 'J') || 'Y' || Barmontype As Flag
From Purchase p
Where p.Fstatus = '采购'
And p.Materialtype In ('材料', '机电')
And p.Barmontype In ('RMB', 'USD', 'EUR')
And p.Deliverydate Between Thisyear And Thisdate
Union
Select Nvl(Sum(p.Barsumcash), 0),
Decode(Materialtype, '材料', 'C', '机电', 'J') || 'M' || Barmontype As Flag
From Purchase p
Where p.Fstatus = '采购'
And p.Materialtype In ('材料', '机电')
And p.Barmontype In ('RMB', 'USD', 'EUR')
And p.Deliverydate Between Thismonth And Thisdate
Decode(Materialtype, '材料', 'C', '机电', 'J') || 'Y' || Barmontype As Flag
From Purchase p
Where p.Fstatus = '采购'
And p.Materialtype In ('材料', '机电')
And p.Barmontype In ('RMB', 'USD', 'EUR')
And p.Deliverydate Between Thisyear And Thisdate
Union
Select Nvl(Sum(p.Barsumcash), 0),
Decode(Materialtype, '材料', 'C', '机电', 'J') || 'M' || Barmontype As Flag
From Purchase p
Where p.Fstatus = '采购'
And p.Materialtype In ('材料', '机电')
And p.Barmontype In ('RMB', 'USD', 'EUR')
And p.Deliverydate Between Thismonth And Thisdate