洞妖洞妖呼叫洞拐
2017-07-25 03:18
采纳率: 76.9%
浏览 1.3k

使用groupby和sum咋没有求和,AsnewWeight是想求一个月的重量求和,咋也显示为空

select convert(char(10),OperateDt,120) as OperateDt,FurnaceNoVch,SiliconLenthVch,CateNameVch,size,WeightDbl1,WeightDbl2,sum(WeightDbl1) as newWeightDbl1,sum(WeightDbl2) as newWeightDbl2,(WeightDbl1+WeightDbl2) as AsnewWeightDbl, FurnaceNoVch2,FallDownName,MasterNameVch,MasterCodeVch
from View_YRemoveRecord where 1=1 group by OperateDt,FurnaceNoVch,SiliconLenthVch,CateNameVch,size,WeightDbl1,WeightDbl2,FurnaceNoVch2,FallDownName,MasterNameVch,MasterCodeVch

图片说明

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

9条回答 默认 最新

  • 张大教主 2017-07-25 06:23
    最佳回答
    //也可以原纪录和汇总值多重组合,加载所有记录,每条记录都有汇总字段值,没有主键id,只能多条件匹配了
    select 
    convert(char(10),a.OperateDt,120) as OperateDt,
    a.FurnaceNoVch,
    a.SiliconLenthVch,
    a.CateNameVch,
    a.size,
    a.WeightDbl1,
    a.WeightDbl2,
    (case a.WeightDbl1 when null then 0 else a.WeightDbl1 end)+(case a.WeightDbl2 when null then 0 else a.WeightDbl2 end) AsnewWeightDbl,
    b.newWeightDbl1,
    b.newWeightDbl2, 
    a.FurnaceNoVch2,
    a.FallDownName,
    a.MasterNameVch,
    a.MasterCodeVch 
    from View_YRemoveRecord a
    
    left join
    (select convert(char(10),OperateDt,120) as OperateDt,
    FurnaceNoVch,
    SiliconLenthVch,
    CateNameVch,
    size,
    sum(case WeightDbl1 when null then 0 else WeightDbl1 end) as newWeightDbl1,
    sum(case WeightDbl2 when null then 0 else WeightDbl2 end) as newWeightDbl2, 
    FurnaceNoVch2,
    FallDownName,
    MasterNameVch,
    MasterCodeVch 
    from View_YRemoveRecord 
    where 1=1 
    group by 
    convert(char(10),OperateDt,120),
    FurnaceNoVch,
    SiliconLenthVch,
    CateNameVch,
    size,
    FurnaceNoVch2,
    FallDownName,
    MasterNameVch,
    MasterCodeVch)b
    
    on convert(char(10),a.OperateDt,120)=b.OperateDt
    and a.FurnaceNoVch=b.FurnaceNoVch
    and a.SiliconLenthVch=b.SiliconLenthVch
    and a.CateNameVch=b.CateNameVch
    and a.size=b.size
    and a.FurnaceNoVch2=b.FurnaceNoVch2
    and a.FallDownName=b.FallDownName
    and a.MasterNameVch=b.MasterNameVch
    and a.MasterCodeVch=b.MasterCodeVch
    
    评论
    解决 无用
    打赏 举报
查看更多回答(8条)

相关推荐 更多相似问题