工作记录

大类成本销售查询

select
decode(substr(n5002,3,2),'25','23','27','23','29','23',substr(n5002,3,2)) "大类编码",
a1002 "大类名称",

round(sum(n5011*(1-nvl(zkl,0)*0.01))/1,2) "总销售额",
round(sum(decode(n5014,'17',(n5011/1.17*0.17)*(1-nvl(zkl,0)*0.01),0))/1,2) "17%税金",
round(sum(decode(n5014,'10',(n5011/1.13*0.13)*(1-nvl(zkl,0)*0.01),'13',(n5011/1.13*0.13)*(1-nvl(zkl,0)*0.01),0))/1,2) "13%税金",
round(sum(decode(n5014,'17',(n5011/1.17),'0',(n5011/1.0),'10',(n5011/1.13),'13',(n5011/1.13),0)*(1-nvl(zkl,0)*0.01))/1,2) "不含税销售总额",
round(sum(decode(n5014,'17',(n5011/1.17),'0',(n5011/1.0),'10',(n5011/1.13),'13',(n5011/1.13),0)*(1-nvl(zkl,0)*0.01))/1,2)-round(sum(n5016*(1-nvl(cbzkl,0)*0.01))/1,2) "总成本",
round(sum(n5016*(1-nvl(cbzkl,0)*0.01))/1,2) "总毛利",
round(decode(sum(n5016*(1-nvl(cbzkl,0)*0.01)),0,0,sum(n5016*(1-nvl(cbzkl,0)*0.01))/decode(sum(decode(n5014,'17',(n5011/1.17),'0',(n5011/1.0),'10',(n5011/1.13),'13',(n5011/1.13),0)*(1-nvl(zkl,0)*0.01)),0,sum(n5016*(1-nvl(cbzkl,0)*0.01)),sum(decode(n5014,'17',(n5011/1.17),'0',(n5011/1.0),'10',(n5011/1.13),'13',(n5011/1.13),0)*(1-nvl(zkl,0)*0.01)))),4)*100 "毛利率" ,
n5010 "日期",
n5001 "门店"
from (select * from fzdc.n501003 union all select * from fzdc.n50),fzdc.cwzkb,a10
where to_char(n5010,'yyyy.mm.dd')>='2010.03.01' and to_char(n5010,'yyyy.mm.dd')<='2010.03.31'
and n5002=bm(+)
and to_char(n5010,'yyyy.mm.dd')=to_char(rq(+),'yyyy.mm.dd')
and n5001=md(+)
and n5002<>'2025'
and n5001||n5002<>'012027'
and n5001 in ('02','07','12')
and decode(substr(n5002,3,2),'25','23','27','23','29','23',substr(n5002,3,2))=a1001
and n5005 in ('1','2','9','3')
and n5006 like '%%'
and n5007 like '%%'
and n5019 like '%%'
group by decode(substr(n5002,3,2),'25','23','27','23','29','23',substr(n5002,3,2)),a1002,n5010,n5001
union all
select substr(n6002,1,2)"大类编码", ' ' "大类名称",-sum(n6004),0,0,-sum(n6004),0,-sum(n6005),0,n6003 "日期",n6001 "门店" from fzdc.n60
where to_char(n6003,'yyyy.mm.dd')>='2010.03.01' and to_char(n6003,'yyyy.mm.dd')<='2010.03.31'
and n6001 in ('02','07','12')
group by substr(n6002,1,2),n6003,n6001

大类成本new 查询

select
decode(substr(n5002,3,2),'25','23','27','23','29','23',substr(n5002,3,2)) "大类编码",
a1002 "大类名称",

round(sum(decode(n5005,'0',n5011,'6',n5011,'7',n5011,'8',n5011,n5011*(1-nvl(zkl,0)*0.01)))/1,2) "总销售额",
round(sum(decode(n5014,'17',decode(n5005,'0',(n5011/1.17*0.17),'6',(n5011/1.17*0.17),'7',(n5011/1.17*0.17),'8',(n5011/1.17*0.17),(n5011/1.17*0.17)*(1-nvl(zkl,0)*0.01)),0))/1,2) "17%税金",
round(sum(decode(n5014,'10',decode(n5005,'0',(n5011/1.13*0.13),'6',(n5011/1.13*0.13),'7',(n5011/1.13*0.13),'8',(n5011/1.13*0.13),(n5011/1.13*0.13)*(1-nvl(zkl,0)*0.01)),
'13',decode(n5005,'0',(n5011/1.13*0.13),'6',(n5011/1.13*0.13),'7',(n5011/1.13*0.13),'8',(n5011/1.13*0.13),(n5011/1.13*0.13)*(1-nvl(zkl,0)*0.01)),0))/1,2) "13%税金",
round(sum(decode(n5014,'17',(n5011/1.17),'0',(n5011/1.0),'10',(n5011/1.13),'13',(n5011/1.13),0)*decode(n5005,'0',1,'6',1,'7',1,'8',1,(1-nvl(zkl,0)*0.01)))/1,2) "不含税销售总额",
round(sum(decode(n5005,'0',n5015,n5015*(1-nvl(cbzkl*0.01,0))))/1,2) "总成本",
round(sum(decode(n5014,'17',(n5011/1.17),'0',(n5011/1.0),'10',(n5011/1.13),'13',(n5011/1.13),0)*decode(n5005,'0',1,'6',1,'7',1,'8',1,(1-nvl(zkl,0)*0.01)))/1,2)-round(sum(decode(n5005,'0',n5015,n5015*(1-nvl(cbzkl*0.01,0))))/1,2) "总毛利",
round(decode(round(sum(decode(n5014,'17',(n5011/1.17),'0',(n5011/1.0),'10',(n5011/1.13),'13',(n5011/1.13),0)*decode(n5005,'0',1,'6',1,'7',1,'8',1,(1-nvl(zkl,0)*0.01)))/1,2)-round(sum(decode(n5005,'0',n5015,n5015*(1-nvl(cbzkl*0.01,0))))/1,2),0,0,round(sum(decode(n5014,'17',(n5011/1.17),'0',(n5011/1.0),'10',(n5011/1.13),'13',(n5011/1.13),0)*decode(n5005,'0',1,'6',1,'7',1,'8',1,(1-nvl(zkl,0)*0.01)))/1,2)-round(sum(decode(n5005,'0',n5015,n5015*(1-nvl(cbzkl*0.01,0))))/1,2)/decode(round(sum(decode(n5014,'17',(n5011/1.17),'0',(n5011/1.0),'10',(n5011/1.13),'13',(n5011/1.13),0)*decode(n5005,'0',1,'6',1,'7',1,'8',1,(1-nvl(zkl,0)*0.01)))/1,2),0,round(sum(decode(n5014,'17',(n5011/1.17),'0',(n5011/1.0),'10',(n5011/1.13),'13',(n5011/1.13),0)*decode(n5005,'0',1,'6',1,'7',1,'8',1,(1-nvl(zkl,0)*0.01)))/1,2)-round(sum(decode(n5005,'0',n5015,n5015*(1-nvl(cbzkl*0.01,0))))/1,2),round(sum(decode(n5014,'17',(n5011/1.17),'0',(n5011/1.0),'10',(n5011/1.13),'13',(n5011/1.13),0)*decode(n5005,'0',1,'6',1,'7',1,'8',1,(1-nvl(zkl,0)*0.01)))/1,2))),4)*100
"毛利率" ,
n5010 "日期"
from fzdc.n50cw,fzdc.cwzkb2,a10
where to_char(n5010,'yyyy.mm.dd')>='2010.02.01' and to_char(n5010,'yyyy.mm.dd')<='2010.02.28'
and n5002=bm(+)
and to_char(n5010,'yyyy.mm.dd')=to_char(rq(+),'yyyy.mm.dd')
and n5001=md(+)
and n5002<>'2025'
and n5001||n5002<>'012027'
and n5001 like '12%'
decode(substr(n5002,3,2),'25','23','27','23','29','23',substr(n5002,3,2))=a1001
and n5005 in ('1','2','9','3')
group by decode(substr(n5002,3,2),'25','23','27','23','29','23',substr(n5002,3,2)),a1002,n5010
union all
select substr(n6002,1,2)"大类编码", ' ' "大类名称",-sum(n6004),0,0,-sum(n6004),0,-sum(n6005),0,n6003 "日期" from fzdc.n60
where to_char(n6003,'yyyy.mm.dd')>='2010.02.01' and to_char(n6003,'yyyy.mm.dd')<='2010.02.28'
and n6001 like '12%'
group by substr(n6002,1,2) ,n6003