select t3.fnumber as 物料编码,
(case t4.FName when '一级销售商' then t1.FPrice else 0 end )一级销售价,
(case t4.FName when '二级销售商' then t1.FPrice else 0 end )二级销售价,
(case t4.FName when '三级销售商' then t1.FPrice else 0 end )三级销售价,
(case t4.FName when '四级销售商' then t1.FPrice else 0 end )四级销售价,
(case t4.FName when '五级销售商' then t1.FPrice else 0 end )五级销售价
from icprcplyentry t1
inner join icprcply t2 on t1.finterid=t2.finterid
inner join t_icitem t3 on t3.fitemid=t1.fitemid
inner join t_submessage t4 on t4.finterid=t1.frelatedid
where t2.FName='22' and t3.FItemID='1002'
物料编码 一级销售价 二级销售价 三级销售价 四级销售价 五级销售价
A.01.01.001 77.0000000000 0.0000000000 0.0000000000 0.0000000000 0.0000000000
A.01.01.001 0.0000000000 77.0000000000 0.0000000000 0.0000000000 0.0000000000
我想要的结果是
物料编码 一级销售价 二级销售价 三级销售价 四级销售价 五级销售价
A.01.01.001 77.0000000000 77.0000000000 0.0000000000 0.0000000000 0.0000000000
------解决方案--------------------
- SQL code
--;WITHDD AS(select t3.fnumber as 物料编码,(case t4.FName when '一级销售商' then t1.FPrice else 0 end )一级销售价,(case t4.FName when '二级销售商' then t1.FPrice else 0 end )二级销售价,(case t4.FName when '三级销售商' then t1.FPrice else 0 end )三级销售价,(case t4.FName when '四级销售商' then t1.FPrice else 0 end )四级销售价, (case t4.FName when '五级销售商' then t1.FPrice else 0 end )五级销售价 from icprcplyentry t1 inner join icprcply t2 on t1.finterid=t2.finterid inner join t_icitem t3 on t3.fitemid=t1.fitemid inner join t_submessage t4 on t4.finterid=t1.frelatedid where t2.FName='22' and t3.FItemID='1002' ) SELECT A.物料编码,CASE WHEN A.一级销售价=0 THEN B.一级销售价 ELSE A.一级销售价 END, CASE WHEN A.二级销售价=0 THEN B.二级销售价 ELSE A.二级销售价 END, CASE WHEN A.三级销售价=0 THEN B.三级销售价 ELSE A.三级销售价 END, CASE WHEN A.四级销售价=0 THEN B.四级销售价 ELSE A.四级销售价 END, CASE WHEN A.五级销售价=0 THEN B.五级销售价 ELSE A.五级销售价 END, FROM DD A INNER JOIN DD B ON A.物料编码=B.物料编码
------解决方案--------------------
- SQL code
select t3.fnumber as 物料编码,max(case t4.FName when '一级销售商' then t1.FPrice else 0 end )一级销售价,max(case t4.FName when '二级销售商' then t1.FPrice else 0 end )二级销售价,max(case t4.FName when '三级销售商' then t1.FPrice else 0 end )三级销售价,max(case t4.FName when '四级销售商' then t1.FPrice else 0 end )四级销售价, max(case t4.FName when '五级销售商' then t1.FPrice else 0 end )五级销售价 from icprcplyentry t1 inner join icprcply t2 on t1.finterid=t2.finterid inner join t_icitem t3 on t3.fitemid=t1.fitemid inner join t_submessage t4 on t4.finterid=t1.frelatedid where t2.FName='22' and t3.FItemID='1002'group by t3.fnumber
------解决方案--------------------
- SQL code
select t3.fnumber as 物料编码,sum(case t4.FName when '一级销售商' then t1.FPrice else 0 end )一级销售价,sum(case t4.FName when '二级销售商' then t1.FPrice else 0 end )二级销售价,sum(case t4.FName when '三级销售商' then t1.FPrice else 0 end )三级销售价,sum(case t4.FName when '四级销售商' then t1.FPrice else 0 end )四级销售价, sum(case t4.FName when '五级销售商' then t1.FPrice else 0 end )五级销售价 from icprcplyentry t1 inner join icprcply t2 on t1.finterid=t2.finterid inner join t_icitem t3 on t3.fitemid=t1.fitemid inner join t_submessage t4 on t4.finterid=t1.frelatedid where t2.FName='22' and t3.FItemID='1002'group by t3.fnumber