执行存储过程的时候出现 警告: 聚合或其它 SET 操作消除了空值。

[写代码养猫]

CREATE Proc GetResultFee
@YearNo varchar(4),
@MonthNo varchar(2)
AS
BEGIN
Insert Into TB_EmpFeeResult Select A.工号,B.statYear AS 统计年份,B.statMonth AS 统计月份,A.纯计件录入工资,A.纯计件加班工资,B.年工,
C.奖金,D.扣罚,E.所得税,F.InsuranceFee AS 缴纳保险,G.ResultFee AS 寝室费用,0 AS 实发工资
FROM TB_JobSalary A,V_GetYearFee B,V_GetReWardFee C,V_GetPenFee D,V_GetTaxFee E,V_GetInsFee F,V_GetRoomFee G
WHERE A.工号=B.EmpNo AND A.工号=C.EmpNo AND A.工号=D.EmpNo AND A.工号=E.EmpNo AND A.工号=F.EmpNo AND A.工号=G.EmpNo 
AND B.statYear=@YearNo AND B.statMonth=@MonthNo
END;
GO

帮忙分析下原因

[dawugui]

无所谓,就是忽略了空值.没关系的.

[百年树人]

这是因为你相加的数值里含有空值，对你的结果没影响的
举个例子

select 
  sum(col) 
from 
   (
    select 1 as col union 
    select null union 
    select 3
    )t

/**
----------- 
4

（所影响的行数为 1 行）

警告: 聚合或其它 SET 操作消除了空值。
**/

[ChinaJiaBing]

 换成 select isnull(字段,0)

[写代码养猫]

但是我直接

Select A.工号,B.statYear AS 统计年份,B.statMonth AS 统计月份,A.纯计件录入工资,A.纯计件加班工资,B.年工,
C.奖金,D.扣罚,E.所得税,F.InsuranceFee AS 缴纳保险,G.ResultFee AS 寝室费用,0 AS 实发工资
FROM TB_JobSalary A,V_GetYearFee B,V_GetReWardFee C,V_GetPenFee D,V_GetTaxFee E,V_GetInsFee F,V_GetRoomFee G
WHERE A.工号=B.EmpNo AND A.工号=C.EmpNo AND A.工号=D.EmpNo AND A.工号=E.EmpNo AND A.工号=F.EmpNo AND A.工号=G.EmpNo 
AND B.statYear=@YearNo AND B.statMonth=@MonthNo

的时候缺有值，虽然他也提示错误

[写代码养猫]

顶下 等待解决！

[noenoughmemory]

不懂，up

[cqsxdb]

不懂，up

[写代码养猫]

Select A.工号,B.statYear AS 统计年份,B.statMonth AS 统计月份,A.纯计件录入工资,A.纯计件加班工资,B.年工,
C.奖金,D.扣罚,E.所得税,F.InsuranceFee AS 缴纳保险,G.ResultFee AS 寝室费用,0 AS 实发工资
FROM TB_JobSalary A,V_GetYearFee B,V_GetReWardFee C,V_GetPenFee D,V_GetTaxFee E,V_GetInsFee F,V_GetRoomFee G
WHERE A.工号=B.EmpNo AND A.工号=C.EmpNo AND A.工号=D.EmpNo AND A.工号=E.EmpNo AND A.工号=F.EmpNo AND A.工号=G.EmpNo 
AND B.statYear=2009 AND B.statMonth=01

直接查询的结果为
5177 2009 01 11.3350 .0000 10 .00 .00 .000 0 .000000000 0
5403 2009 01 33.8350 1.0691 10 .00 .00 .000 0 .000000000 0
5408 2009 01 36.8350 2.5691 30 .00 .00 .000 0 .000000000 0

[百年树人]

这个问题估计跟空值没有关系，看看TB_EmpFeeResult表的字段个数和查询结果的个数是否一致，各字段的类型是否一样，如类型不一样需要转换类型

[q522425036]

SET ANSI_WARNINGS OFF
将警告关闭，如是 ON 则是开启警告