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

使用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条)

报告相同问题?

悬赏问题

  • ¥50 树莓派安卓APK系统签名
  • ¥15 maple软件,用solve求反函数出现rootof,怎么办?
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波
  • ¥15 针对曲面部件的制孔路径规划,大家有什么思路吗