代码之家 › 专栏 › 技术社区 › user215675

C#-使用LINQ的日期总计和GrandTotal

linq c#

user215675 · 技术社区 · 16 年前

(***) 不适用时)。

OrderID     OrderAmt   OrderDate                                              
----------- ---------- --------------------
1           10.50      2003-10-11 08:00:00
2           11.50      2003-10-11 10:00:00
3           1.25       2003-10-11 12:00:00
4           100.57     2003-10-12 09:00:00
5           19.99      2003-10-12 11:00:00
6           47.14      2003-10-13 10:00:00
7           10.08      2003-10-13 12:00:00
8           7.50       2003-10-13 19:00:00
9           9.50       2003-10-13 21:00:00

输出

OrderID     Order Date OrderAmt   Sub Total  Grand Total 
----------- ---------- ---------- ---------- ----------- 
1           10/11/2003 10.50       ***         ***            
2           10/11/2003 11.50       ***         ***           
3           10/11/2003 1.25       23.25        ***        
4           10/12/2003 100.57      ***         ***            
5           10/12/2003 19.99      120.56       ***        
6           10/13/2003 47.14      ***          ***           
7           10/13/2003 10.08      ***          ***           
8           10/13/2003 7.50       ***          ***           
9           10/13/2003 9.50       74.22      218.03

var qry =
                (from o in ord

                 select new
                 {

                     OrdNumber = o.OrderNumber,
                     OrdDate = o.OrderDate,
                     Amount = o.OrderAmount,
                     GrandTotal = ord.Sum(p => p.OrderAmount)
                 }
                  ).ToList();

2 回复 | 直到 16 年前

Yannick Motton 16 年前

var SubTotals = from o in ord
                group o by o.OrderDate.Date into g
                    select new
                               {
                                   Id = g.Max(x => x.OrderNumber),
                                   Amount = g.Sum(x => x.OrderAmount)
                               };
var qry = from o in ord
          orderby o.OrderDate
          let subTotals = SubTotals.Where(x => x.Id == o.OrderNumber)
          let grandTotal = ord.Sum(x => x.OrderAmount)
          let lastId = ord.OrderBy(x => x.OrderNumber).Last().OrderNumber
          select
              new
                  {
                      OrderNumber = o.OrderNumber,
                      OrderDate = o.OrderDate.Date,
                      Amount = o.OrderAmount,
                      SubTotal = (subTotals.Any()
                                    ? subTotals.First().Amount.ToString()
                                    : "***"),
                      GrandTotal = (o.OrderNumber == lastId
                                    ? grandTotal.ToString()
                                    : "***")
                  };

作为一般免责声明,我敦促您重新考虑使用linq格式化数据。如果您只想使用SQL,那么对此类代码进行黑客攻击是可以的,但如果有机会,只在数据集上循环也没有什么害处:-)

Adriaan Stander 16 年前

这将按数据分组,并在列表上显示总计

public class MyClass
{
    public int OrderID;
    public decimal OrderAmount;
    public DateTime OrderDate;
}

List<MyClass> list = new List<MyClass>();
list.Add(new MyClass { OrderID = 1, OrderAmount = 10.50m, OrderDate = new DateTime(2009, 10, 11, 8, 0, 0) });
list.Add(new MyClass { OrderID = 1, OrderAmount = 11.50m, OrderDate = new DateTime(2009, 10, 11, 10, 0, 0) });
list.Add(new MyClass { OrderID = 1, OrderAmount = 1.25m, OrderDate = new DateTime(2009, 10, 11, 12, 0, 0) });

list.Add(new MyClass { OrderID = 1, OrderAmount = 100.57m, OrderDate = new DateTime(2009, 10, 12, 09, 0, 0) });
list.Add(new MyClass { OrderID = 1, OrderAmount = 19.99m, OrderDate = new DateTime(2009, 10, 12, 11, 0, 0) });

var t = from l in list
    group l by l.OrderDate.Date into g
    let sum = g.Sum(x => x.OrderAmount)
    select new { g.Key, sum , GrandTotal = list.Sum(x => x.OrderAmount) };

不确定要如何将其转换为上面所需的格式?