--食品部销售后百分之十

insert into wyl_spbl
select n5001,n5002,n5004,n5019,n5011,flje,flpm/flsl pmbl,flpm,flsl
from (select n5001,n5002,n5004,n5019,n5011,
sum(n5011) over (partition by n5001,n5004 order by n5001,n5004) flje,
row_number() over(partition by n5001,n5004 order by n5001,n5004,n5011 desc) flpm,
count(n5019) over(partition by n5001,n5004 order by n5001,n5004) flsl
from (select n5001,n5002,n5004,n5019,sum(n5011) n5011
from (select * from n501001 where n5002 like '10%'
union all select * from n501002 where n5002 like '10%'
union all select * from n50 where n5002 like '10%'),c01d
where n5001=c01d00 and n5019=c01d01 and n5001 <>'99'
and c01d27 in ('2','8')
and to_char(n5010,'yyyymmdd') between '20100101' and '20100331'
group by n5001,n5002,n5004,n5019))

select wyl_spa.*,decode(bl,null,'','是') from wyl_spa,
(select * from wyl_spbl where bl>=0.9 and n5001='16')
where c01d26=n5002(+) and c01d01=n5019(+)
order by xh

select c01d00 门店,c01d26 部门,c01d33 分类编码,a1002 分类名称,c01d01 商品编码,c01d21 商品名称,
c01d40 规格,c01d02 进价,c01d03 售价,c01d12 库存数量,
decode(c01d31,'1','潜在','2','新品','3','正常','4','崭停下单','5','崭停付款','6','崭停下单付款','7','终止') 商品状态
from (select wyl_spbl.*,a1002 from wyl_spbl,a10 where n5004=a1001 and bl>=0.9),c01d
where n5001=c01d00 and n5019=c01d01
order by c01d26,c01d00,c01d33,c01d01

select n5001 门店,n5002 部门,n5004 分类编码,a1002 分类名称,count(n5019) 单品数 from
(select wyl_spbl.*,a1002 from wyl_spbl,a10 where n5004=a1001 and bl>=0.9)
group by n5001,n5002,n5004,a1002
order by n5002,n5001,n5004