聚合(CaseWhen(聚合)) 解决方案

 selectUnsubmittedCount=(casewhenApproveStatusin('未提交','已提交')thencount(FormID)end),

 
 ApprovedCount=( 
 case 
 when 
 ApproveStatus 
 in 
 ( 
 '已审核' 
 ) 
 then 
 count 
 (FormID) 
 end 
 ) 

from 
 formLogistic 

group 
 by 
 ApproveStatus 

返回结果集为GroupBy后的多行数据，如何避开GroupBy? 

  可以使用max()或者sum()的方式，将casewhen一行化，如： 

 
  select 
  UnsubmittedCount= 
  max 
  ( 
  case 
  when 
  ApproveStatus 
  in 
  ( 
  '未提交' 
  sql plain" style="list-style:none; margin:0px!important; padding:0px!important; border:0px!important; outline:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; line-height:1.1em!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas, 
  '已提交' 
  ) 
  then 
  count 
  (FormID) 
  end 
  ) 
 
from 
  formLogistic 
 
group 
  by 
  ApproveStatus 
 
但：聚合(CaseWhen(聚合)) 这种模式的查询方式，
 
会报一个错误：不能对包含聚合或子查询的表达式执行聚合函数。

可以优化为： 

 
 select 
 UnsubmittedCount= 
 COUNT 
 ( 
 case 
 when 
 ApproveStatus 
 in 
 ( 
 '未提交' 
 sql plain" style="list-style:none; font-size:1em!important; line-height:1.1em!important; margin:0px!important; padding:0px!important; border:0px!important; outline:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas, 
 '已提交' 
 ) 
 then 
 FormID 
 end 
 sql plain" style="list-style:none; font-size:1em!important; line-height:1.1em!important; margin:0px!important; padding:0px!important; border:0px!important; outline:0px!important; bottom:auto!important; float:none!important; height:auto!important; left:auto!important; overflow:visible!important; position:static!important; right:auto!important; top:auto!important; vertical-align:baseline!important; width:auto!important; font-family:Consolas, 

 
  ApprovedCount= 
  COUNT 
  '已审核' 
  then 
  FormID 
  ) 
 
formLogistic 
 

聚合(CaseWhen(聚合)) 解决方案

猜你在找的设计模式相关文章