Sqlsugar的case when语法用于数据统计实战

1.三元运算符方式

//只统计数量,所以固定返回1和0;
var query = Db.Queryable<Orders,Service>((a, b) => new object[] {
                JoinType.Left,a.OrderID==b.OrderID
            });
 var infoDTO = query.Select((a, b) => new ServiceStatusCountDTO
             {
-                ConfirmCount = SqlFunc.AggregateSum(SqlFunc.IIF(a.ServiceStatus== Sea.Enum.OrderFlowStatus.dai_que_ren.GetStringValue(),1,0)),
-                HistoryCount = SqlFunc.AggregateSum(SqlFunc.IIF(a.ServiceStatus == Sea.Enum.OrderFlowStatus.yi_yan_shou.GetStringValue() && b.WarrantyExpiredDate < nowTime, 1, 0))
}).First();

2.if else方式

//此处示例没有else判断,省略了部分
var query = Db.Queryable<Orders,Service>((a, b) => new object[] {
                JoinType.Left,a.OrderID==b.OrderID
            });
var infoDTO = query.Select((a, b) => new ServiceStatusCountDTO
            {
                ConfirmCount = SqlFunc.AggregateSum(SqlFunc.IF(a.ServiceStatus== Sea.Enum.OrderFlowStatus.dai_que_ren.GetStringValue()).Return(1).End(0)),
                CompletedCount = SqlFunc.AggregateSum(SqlFunc.IF(a.ServiceStatus == Sea.Enum.OrderFlowStatus.yi_yan_shou.GetStringValue() && b.WarrantyExpiredDate >= nowTime).Return(1).End(0))
 }).First(); 

3.使用group by代替case when

4. group by和case when 在数据统计时性能上的区别