sql - 汇总函数 - 替换 NULL

Question

我似乎在处理 SQL 的最后部分时遇到了问题。当在我的 SQL 中使用汇总函数实现分组依据时，出现 NULL。如何修改 NULL 以便在此聚合 SQL 中将其替换为 TOTAL？

Current table returned
Name Activate Deactivate
Max      5       2
TAX     12       5
NULL    17       8

select 
    case 
    when (upper(m.email) like '%max.com') then 'MAX'
    when (upper(m.email) like '%tax.com') then 'TAX'
    else 'OTHER'
end Name, 
SUM(case when substring(convert(varchar(8),n.created_on,112),1,6) = '201209' then 1 else 0         end) 'Activated',
   SUM(case when substring(convert(varchar(8),m.LastLockoutDate,112),1,6)='201209' then 1 else 0              end) 'Deactivated'
           from membership.user_details d
          inner join membership.aspnet_membership m 
on m.userid = d.userid
        inner join membership.user_notes n 
         on n.userid = d.userid
        and n.CREATED_ON = (select min(created_on) 
                    from membership.user_notes 
                    where userid = n.userid
                        and note = 'received.')  
         where approved = 1
         group by case when (upper(m.email) like '%max.com') then 'MAX'
      when (upper(m.email) like '%tax.com') then 'TAX'
      else 'OTHER' end     
   with RollUp 

Answer 1

ROLLUP 在总计行中放置了一个 null，所以如果您想替换它，我建议您将现有查询放在一个子查询中，然后使用NAME 上的 CASE 将 null 替换为 Total。

您的代码将与此类似:

select 
  case when name is null then 'Total' else name end Name,
  sum(Activated) Activated,
  sum(Deactivated) Deactivated
from
(
  select 
    case 
      when (upper(m.email) like '%max.com') then 'MAX'
      when (upper(m.email) like '%tax.com') then 'TAX'
      else 'OTHER'
    end Name, 
  SUM(case when substring(convert(varchar(8),n.created_on,112),1,6) = '201209' then 1 else 0         end) 'Activated',
  SUM(case when substring(convert(varchar(8),m.LastLockoutDate,112),1,6)='201209' then 1 else 0              end) 'Deactivated'
  from membership.user_details d
  inner join membership.aspnet_membership m 
    on m.userid = d.userid
  inner join membership.user_notes n 
    on n.userid = d.userid
    and n.CREATED_ON = (select min(created_on) 
                        from membership.user_notes 
                        where userid = n.userid
                          and note = 'received.')  
  where approved = 1
  group by case when (upper(m.email) like '%max.com') then 'MAX'
        when (upper(m.email) like '%tax.com') then 'TAX'
        else 'OTHER' end     
) src
group by name with rollup

如果您不将查询包装在子查询中，那么您可以使用这样的东西:

select
   case when 
     (case 
        when (upper(email) like '%max.com') then 'MAX'
        when (upper(email) like '%tax.com') then 'TAX'
        else 'OTHER'
      end) is null then 'Total'
      else case 
        when (upper(email) like '%max.com') then 'MAX'
        when (upper(email) like '%tax.com') then 'TAX'
        else 'OTHER'
      end end Name,
  SUM(case when substring(convert(varchar(8),n.created_on,112),1,6) = '201209' then 1 else 0 end) 'Activated',
  SUM(case when substring(convert(varchar(8),m.LastLockoutDate,112),1,6)='201209' then 1 else 0 end) 'Deactivated'
from membership.user_details d
inner join membership.aspnet_membership m 
  on m.userid = d.userid
inner join membership.user_notes n 
  on n.userid = d.userid
  and n.CREATED_ON = (select min(created_on) 
                     from membership.user_notes 
                     where userid = n.userid
                       and note = 'received.')  
where approved = 1
group by case when (upper(m.email) like '%max.com') then 'MAX'
        when (upper(m.email) like '%tax.com') then 'TAX'
        else 'OTHER' end with rollup