8,494
社区成员




SELECT [One_project],[Two_project],[Three_project],[XM_ID],[TR_Year],SUM (S_TR) AS S_TRCount,SUM (ZY_TR) AS ZY_TRCount,SUM (HY_TR) AS HY_TRCount,SUM (QT_TR) AS QT_TRCount,SUM(TR_SUM) AS sum_TRCount FROM [input] WHERE OneHeadID = '7' GROUP BY One_project,Two_project,Three_project,XM_ID,TR_Year WITH rollup HAVING XM_ID IS NOT NULL
/// <summary>
/// Adds sub-totals to a list of items, along with a grand total for the whole list.
/// </summary>
/// <param name="elements">Group and/or sort this yourself before calling WithRollup.</param>
/// <param name="primaryKeyOfElement">Given a TElement, return the property that you want sub-totals for.</param>
/// <param name="calculateSubTotalElement">Given a group of elements, return a TElement that represents the sub-total.</param>
/// <param name="grandTotalElement">A TElement that represents the grand total.</param>
public static List<TElement> WithRollup<TElement, TKey>(this IEnumerable<TElement> elements,
Func<TElement, TKey> primaryKeyOfElement,
Func<IGrouping<TKey, TElement>, TElement> calculateSubTotalElement,
TElement grandTotalElement)
{
// Create a new list the items, subtotals, and the grand total.
List<TElement> results = new List<TElement>();
var lookup = elements.ToLookup(primaryKeyOfElement);
foreach (var group in lookup)
{
// Add items in the current group
results.AddRange(group);
// Add subTotal for current group
results.Add(calculateSubTotalElement(group));
}
// Add grand total
results.Add(grandTotalElement);
return results;
}
Here is the Sample:
class Program
{
static void Main(string[] args)
{
IQueryable<CustomObject> dataItems = (new[]
{
new CustomObject { City = "Seattle", Plan = "Plan B", Charges = 20 },
new CustomObject { City = "Seattle", Plan = "Plan A", Charges = 10 },
new CustomObject { City = "Seattle", Plan = "Plan B", Charges = 20 },
new CustomObject { City = "Seattle", Plan = "Plan A", Charges = 10 },
new CustomObject { City = "Seattle", Plan = "Plan A", Charges = 10 },
new CustomObject { City = "Seattle", Plan = "Plan A", Charges = 10 },
new CustomObject { City = "Portland", Plan = "Plan A", Charges = 10 },
new CustomObject { City = "Portland", Plan = "Plan A", Charges = 10 },
new CustomObject { City = "Portland", Plan = "Plan C", Charges = 30 },
new CustomObject { City = "Portland", Plan = "Plan C", Charges = 30 },
new CustomObject { City = "Portland", Plan = "Plan C", Charges = 30 }
}).AsQueryable();
IQueryable<CustomObject> orderedElements = from item in dataItems
orderby item.City, item.Plan
group item by new { item.City, item.Plan } into grouping
select new CustomObject
{
City = grouping.Key.City,
Plan = grouping.Key.Plan,
Charges = grouping.Sum(item => item.Charges),
Count = grouping.Count()
};
List<CustomObject> results = orderedElements.WithRollup(
item => item.City,
group => new CustomObject
{
City = group.Key,
Plan = "All",
Charges = group.Sum(item => item.Charges),
Count = group.Sum(item => item.Count)
},
new CustomObject
{
City = "All",
Plan = "All",
Charges = orderedElements.Sum(item => item.Charges),
Count = orderedElements.Sum(item => item.Count)
});
foreach (var result in results)
Console.WriteLine(result);
Console.Read();
}
}
class CustomObject
{
public string City { get; set; }
public string Plan { get; set; }
public int Count { get; set; }
public decimal Charges { get; set; }
public override string ToString()
{
return String.Format("{0} - {1} ({2} - {3})", City, Plan, Count, Charges);
}
}
var query = from l in list
group l by new {A=l.A,B=l.B} into g
select g;
var query2 = from g in query
group g by g.Key.A into x
select x;
//然后query和query2合并结果