查询的问题 返回
var list = orderRepository.AsQueryable() .LeftJoin<UserBase>((o, u) => o.UserId == u.Id) .LeftJoin<UserBase>((o, u, u1) => o.ExpertId == u1.Id) .LeftJoin<Articles>((o, u, u1, a) => o.OrderType == 1 && o.RelateId == a.Id) .LeftJoin<Album>((o, u, u1, a, b) => o.OrderType == 2 && o.RelateId == b.Id) //.Where((o, u, u1, a, b) => o.OrderStatus == 2 || o.OrderStatus == 3 || o.OrderStatus == 4) .WhereIF(input.KeyType == 1 && !string.IsNullOrWhiteSpace(input.Key), (o, u, u1, a, b) => u.UserName.Contains(input.Key!)) .WhereIF(input.KeyType == 2 && !string.IsNullOrWhiteSpace(input.Key), (o, u, u1, a, b) => u1.UserName.Contains(input.Key!)) .WhereIF(input.KeyType == 3 && !string.IsNullOrWhiteSpace(input.Key), (o, u, u1, a, b) => a.Title.Contains(input.Key!)) .WhereIF(input.KeyType == 4 && !string.IsNullOrWhiteSpace(input.Key), (o, u, u1, a, b) => b.Title.Contains(input.Key!)) .WhereIF(input.PayWay > 0, (o, u, u1, a, b) => o.PaymentMethod == input.PayWay) .WhereIF(input.OrderStatus > 0, (o, u, u1, a, b) => o.OrderType == input.OrderStatus) .WhereIF(input.OrderType == 1, (o, u, u1, a, b) => o.OrderType == 1 && !o.IsReward) .WhereIF(input.OrderType == 2, (o, u, u1, a, b) => o.OrderType == 2 && !o.IsReward) .WhereIF(input.OrderType == 3, (o, u, u1, a, b) => o.OrderStatus == 2 && !o.IsReward) .WhereIF(input.OrderType == 4, (o, u, u1, a, b) => o.IsReward) .WhereIF(input is { DateType: 1, Begin: not null, End: not null }, (o, u, u1, a, b) => o.ModifiedTime >= input.Begin && o.ModifiedTime < input.End) .Select((o, u, u1, a, b) => new FinancialDetailOut { ModifiedDate = o.PaymentTime, Title = string.IsNullOrWhiteSpace(a.Title) ? b.Title : a.Title, ExpertNikeName = u1.NickName, UserName = u.UserName, IsReward = o.IsReward, ActualPayment = o.ActualPayment, Remark = o.Remark, PaymentMethod = o.PaymentMethod, Type = o.OrderType, OrderStatus = o.OrderStatus, ModifiedTime = o.ModifiedTime, UserId=o.UserId }); OrderAmount = list.Where(o => o.OrderStatus == 3 || o.OrderStatus == 4).Sum(o => o.ActualPayment), OrderCount = list.Where(o => o.OrderStatus == 3 || o.OrderStatus == 4).Select(o=>o.UserId).Distinct().Count(), RefundAmount = list.Where(o => o.OrderStatus == 2).Sum(o => o.ActualPayment), BalanceAmount = list.Where(o => (o.OrderStatus == 3 || o.OrderStatus == 4) && o.PaymentMethod == 4).Sum(o => o.ActualPayment), WxAmount = list.Where(o => (o.OrderStatus == 3 || o.OrderStatus == 4) && o.PaymentMethod == 1).Sum(o => o.ActualPayment), ZfbAmount = list.Where(o => (o.OrderStatus == 3 || o.OrderStatus == 4) && o.PaymentMethod == 2).Sum(o => o.ActualPayment),
list结果集在下面复用时,条件会重复拼接,
OrderCount 和把OrderAmount 里面的条件一起用上
这种情况怎么处理比好?
SELECT COUNT(1) FROM (SELECT DISTINCT [o].[Id],[o].[UserId],[o].[RequestId],[o].[OrderNo],[o].[RelateId],[o].[OrderType],[o].[AlbumPriceStrategyId],[o].[IsReward],[o].[ExpertId],[o].[OrderAmount],[o].[DiscountAmount],[o].[ActualPayment],[o].[PaymentMethod],[o].[OrderStatus],[o].[PayOrderNo],[o].[TradeStateDesc],[o].[Remark],[o].[CreatedTime],[o].[ModifiedTime],[o].[PaymentTime],[o].[RefundTime],[o].[ReceiptTime] FROM [UserOrder] [o] Left JOIN [UserBase] [u] ON ( [o].[UserId] = [u].[Id] ) Left JOIN [UserBase] [u1] ON ( [o].[ExpertId] = [u1].[Id] ) Left JOIN [Article] [a] ON (( [o].[OrderType] = 1 ) AND ( [o].[RelateId] = [a].[Id] )) Left JOIN [Album] [b] ON (( [o].[OrderType] = 2 ) AND ( [o].[RelateId] = [b].[Id] )) WHERE (( [o].[OrderStatus] = 3 ) OR ( [o].[OrderStatus] = 4 )) AND (( [o].[OrderStatus] = 3 ) OR ( [o].[OrderStatus] = 4 )) AND ( [o].[OrderStatus] = 2 ) AND ((( [o].[OrderStatus] = 3 ) OR ( [o].[OrderStatus] = 4 )) AND ( [o].[PaymentMethod] = 4 )) AND ((( [o].[OrderStatus] = 3 ) OR ( [o].[OrderStatus] = 4 )) AND ( [o].[PaymentMethod] = 1 )) AND ((( [o].[OrderStatus] = 3 ) OR ( [o].[OrderStatus] = 4 )) AND ( [o].[PaymentMethod] = 2 ))) CountTable
热忱回答(2)
-
fate sta VIP01周前
OrderAmount = list.clone().Where(o => o.OrderStatus == 3 || o.OrderStatus == 4).Sum(o => o.ActualPayment),
OrderCount = list.clone().Where(o => o.OrderStatus == 3 || o.OrderStatus == 4).Select(o=>o.UserId).Distinct().Count(),
RefundAmount = list.clone().Where(o => o.OrderStatus == 2).Sum(o => o.ActualPayment),
需要加clone
0 回复 -
云自无心水自 VIP06天前
@fate sta:谢谢
0 回复