SQL Server Sum Aggregate 函数 - 过滤结果集？

Question

我有一个非常通用的查询，它按 ID 分组并汇总数量并获得最大地址编号。当我运行它时，我得到以下记录数:

SELECT (case when m.participant_id=0 then 100000000 + m.member_id else m.participant_id      END) as PID, 
    max(m.address_no) as address_no, sum(e.volume) as P3M_Vol into dbo.tmp_MC_P3M_FuelsPromo_3      
  from dbo.MonthlyDataMart e 
  inner join dbo.v_sr_member m on e.member_id = m.member_id
  where e.member_type_cd = 'MC' 
    and e.year = 2014 and e.month between 2 and 4 
    and e.tran_code in ('PTSEARN','PREMIUM')
    and not exists (select 1 from dbo.v_DNC d where e.member_id = d.member_id)         
group by (case when m.participant_id=0 then 100000000 + m.member_id else m.participant_id END) 

我收到以下消息:

Warning: Null value is eliminated by an aggregate or other SET operation.

(16828 row(s) affected)

然后我删除了查询中的 sum(e.volume) 列(以下代码:)

SELECT (case when m.participant_id=0 then 100000000 + m.member_id else m.participant_id END) as PID, 
    max(m.address_no) as address_no into dbo.tmp_MC_P3M_FuelsPromo_2        
  from dbo.MonthlyDataMart e 
  inner join dbo.v_sr_member m on e.member_id = m.member_id
  where e.member_type_cd = 'MC' 
    and e.year = 2014 and e.month between 2 and 4 
    and e.tran_code in ('PTSEARN','PREMIUM')
    and not exists (select 1 from dbo.v_DNC d where e.member_id = d.member_id)         
group by (case when m.participant_id=0 then 100000000 + m.member_id else m.participant_id END) 

我得到了不同数量的行!

Warning: Null value is eliminated by an aggregate or other SET operation.

(34023 row(s) affected)

知道为什么会这样吗？我只去掉了聚合函数，行数不一样了。我正在使用 MS SQL 2000。

Answer 1

Max() 和 sum() 函数正在消除此查询中的空值。错误是针对这些函数内的那些列。受影响的行更改是因为没有 sum () inside group by aggrigate functions。祝你好运