求一条复杂的SQL统计语句写法

Question

在这个语句中增加你需要的结果好象不行吧，另生成一个结果集倒是可能：select count(*) 记录总数,
       SUM(毛重) 毛重总数,
       SUM(皮重) 皮重总数,
       SUM(净重) 净重总数,
       SUM(case when 净重 is null then 1 else 0 end) 未回皮数,
       SUM(case when 净重 is null then 0 else 1 end) 已回皮数
   from (
     select PoundMaterial.vMaterialName 货名,
            PoundIntOutUnit.vUnitName 发货方,
            ShouHuoFang.vUnitName 收货方,
            PoundType.vTypeName 装货方,
            Operator.vUserName 称毛重,
            ChengPiZhong.vUserName 称皮重,
            PoundRecord.vRecoreId 磅单号,
            PoundRecord.vCarNo 车号,
            PoundRecord.SkinDate 毛重日期,
            PoundRecord.SkinTime 毛重时间,
            PoundRecord.dDate 皮重日期,
            PoundRecord.dTime 皮重时间,
            PoundRecord.nGrossWei 毛重,
            PoundRecord.nSkin 皮重,
            PoundRecord.nNetWei 净重
       from Operator right join (PoundMaterial right join (PoundType right join (PoundIntOutUnit right join (PoundIntOutUnit ShouHuoFang right join (Operator ChengPiZhong right join PoundRecord on ChengPiZhong.vUserId=PoundRecord.vSkinOperator) on ShouHuoFang.vUnitId=PoundRecord.vInUnitId) on PoundIntOutUnit.vUnitId=PoundRecord.vOutUnitId) on PoundType.vMatTypeId=PoundRecord.vMatTypeId) on PoundMaterial.vMaterialId=PoundRecord.vMaterialId) on Operator.vUserId=PoundRecord.vEmployeeId
       where PoundRecord.SkinDate='2005-10-31'
       order by PoundRecord.vRecoreId) a

Answer 1

把 order by PoundRecord.vRecoreId 去掉后再试试

Answer 2

select PoundMaterial.vMaterialName 货名,
       PoundIntOutUnit.vUnitName 发货方,
       ShouHuoFang.vUnitName 收货方,
       PoundType.vTypeName 装货方,
       Operator.vUserName 称毛重,
       ChengPiZhong.vUserName 称皮重,
       PoundRecord.vRecoreId 磅单号,
       PoundRecord.vCarNo 车号,
       PoundRecord.SkinDate 毛重日期,
       PoundRecord.SkinTime 毛重时间,
       PoundRecord.dDate 皮重日期,
       PoundRecord.dTime 皮重时间,
       PoundRecord.nGrossWei 毛重,
       PoundRecord.nSkin 皮重,
       PoundRecord.nNetWei 净重,
       COUNT(PoundRecord.nGrossWei) 记录总数,
       SUM(PoundRecord.nGrossWei) 毛重总数,
SUM(PoundRecord.nSkin) 皮重总数,
SUM(PoundRecord.nNetWei) 净重总数,
SUM(case when PoundRecord.nSkin is null then 1 else 0 end) 未回皮数,
SUM(case when PoundRecord.nSkin is null then 0 else 1 end) 已回皮数 
from Operator right join (PoundMaterial right join (PoundType right join (PoundIntOutUnit right join (PoundIntOutUnit ShouHuoFang right join (Operator ChengPiZhong right join PoundRecord on ChengPiZhong.vUserId=PoundRecord.vSkinOperator) on ShouHuoFang.vUnitId=PoundRecord.vInUnitId) on PoundIntOutUnit.vUnitId=PoundRecord.vOutUnitId) on PoundType.vMatTypeId=PoundRecord.vMatTypeId) on PoundMaterial.vMaterialId=PoundRecord.vMaterialId) on Operator.vUserId=PoundRecord.vEmployeeId
where PoundRecord.SkinDate='2005-10-31'
order by PoundRecord.vRecoreId

Answer 3

服务器: 消息 8118，级别 16，状态 1，行 1
列 'PoundMaterial.vMaterialName' 在选择列表中无效，因为该列未包含在聚合函数中，并且没有 GROUP BY 子句。所有列都这样！

Answer 4

select count(*) 记录总数,
       SUM(毛重) 毛重总数,
       SUM(皮重) 皮重总数,
       SUM(净重) 净重总数,
       SUM(case when 净重 is null then 1 else 0 end) 未回皮数,
       SUM(case when 净重 is null then 0 else 1 end) 已回皮数
   from (
     select PoundMaterial.vMaterialName 货名,
            PoundIntOutUnit.vUnitName 发货方,
            ShouHuoFang.vUnitName 收货方,
            PoundType.vTypeName 装货方,
            Operator.vUserName 称毛重,
            ChengPiZhong.vUserName 称皮重,
            PoundRecord.vRecoreId 磅单号,
            PoundRecord.vCarNo 车号,
            PoundRecord.SkinDate 毛重日期,
            PoundRecord.SkinTime 毛重时间,
            PoundRecord.dDate 皮重日期,
            PoundRecord.dTime 皮重时间,
            PoundRecord.nGrossWei 毛重,
            PoundRecord.nSkin 皮重,
            PoundRecord.nNetWei 净重
       from Operator right join (PoundMaterial right join (PoundType right join (PoundIntOutUnit right join (PoundIntOutUnit ShouHuoFang right join (Operator ChengPiZhong right join PoundRecord on ChengPiZhong.vUserId=PoundRecord.vSkinOperator) on ShouHuoFang.vUnitId=PoundRecord.vInUnitId) on PoundIntOutUnit.vUnitId=PoundRecord.vOutUnitId) on PoundType.vMatTypeId=PoundRecord.vMatTypeId) on PoundMaterial.vMaterialId=PoundRecord.vMaterialId) on Operator.vUserId=PoundRecord.vEmployeeId
       where PoundRecord.SkinDate='2005-10-31') a