protected List <SaleProduct> Query(Expression <Func <OrderInfo, bool> > _where, out decimal daySale, out decimal totalAmount) { var cabinetList = context.Query <Cabinet>().Where(o => o.hotel == hotelInfo.id).ToList(); var query = context.Query <OrderInfo>().Where(_where).LeftJoin <Product>((a, b) => a.product == b.id) .Select((a, b) => new SaleProduct { name = b.name, code = b.code, price1 = a.price1, id = a.product, saleTime = a.date }); var groupQuery = query.GroupBy(o => o.id).Select(o => new SaleProduct { minTime = AggregateFunctions.Min(o.saleTime), maxTime = AggregateFunctions.Max(o.saleTime), name = AggregateFunctions.Max(o.name), code = AggregateFunctions.Max(o.code), salesCount = AggregateFunctions.Count(), totalAmount = AggregateFunctions.Sum(o.price1) }); var list = groupQuery.ToList(); if (list.Count != 0) { totalAmount = list.Sum(o => o.totalAmount).ObjToInt(0).CentToRMB(0); var totalDay = list.Max(o => o.maxTime).Subtract(list.Min(o => o.minTime)).Days; if (totalDay != 0 && cabinetList.Count != 0) { daySale = Math.Round(totalAmount / totalDay / cabinetList.Count); } else { daySale = 0; } } else { daySale = 0; totalAmount = 0; } return(list); }
protected List <SaleProduct> Query(Expression <Func <OrderInfo, bool> > _where, out int totalSum, out decimal totalAmount) { var query = context.Query <OrderInfo>().Where(_where).LeftJoin <Product>((a, b) => a.product == b.id) .Select((a, b) => new SaleProduct { name = b.name, code = b.code, price1 = a.price1, id = a.product, saleTime = a.date }); var groupQuery = query.GroupBy(o => o.id).Select(o => new SaleProduct { minTime = AggregateFunctions.Min(o.saleTime), maxTime = AggregateFunctions.Max(o.saleTime), name = AggregateFunctions.Max(o.name), code = AggregateFunctions.Max(o.code), salesCount = AggregateFunctions.Count(), totalAmount = AggregateFunctions.Sum(o.price1) }); var list = groupQuery.ToList(); if (list.Count != 0) { totalAmount = list.Sum(o => o.totalAmount).ObjToInt(0).CentToRMB(0); totalSum = list.Sum(o => o.salesCount).ObjToInt(0); } else { totalSum = 0; totalAmount = 0; } return(list); }
public static void AggregateQuery() { IQuery <User> q = context.Query <User>(); q.Select(a => AggregateFunctions.Count()).First(); /* * SELECT COUNT(1) AS "C" FROM "USERS" "USERS" WHERE ROWNUM < 2 */ q.Select(a => new { Count = AggregateFunctions.Count(), LongCount = AggregateFunctions.LongCount(), Sum = AggregateFunctions.Sum(a.Age), Max = AggregateFunctions.Max(a.Age), Min = AggregateFunctions.Min(a.Age), Average = AggregateFunctions.Average(a.Age) }).First(); /* * SELECT COUNT(1) AS "COUNT",COUNT(1) AS "LONGCOUNT",SUM("USERS"."AGE") AS "SUM",MAX("USERS"."AGE") AS "MAX",MIN("USERS"."AGE") AS "MIN",AVG("USERS"."AGE") AS "AVERAGE" FROM "USERS" "USERS" WHERE ROWNUM < 2 */ var count = q.Count(); /* * SELECT COUNT(1) AS "C" FROM "USERS" "USERS" */ var longCount = q.LongCount(); /* * SELECT COUNT(1) AS "C" FROM "USERS" "USERS" */ var sum = q.Sum(a => a.Age); /* * SELECT SUM("USERS"."AGE") AS "C" FROM "USERS" "USERS" */ var max = q.Max(a => a.Age); /* * SELECT MAX("USERS"."AGE") AS "C" FROM "USERS" "USERS" */ var min = q.Min(a => a.Age); /* * SELECT MIN("USERS"."AGE") AS "C" FROM "USERS" "USERS" */ var avg = q.Average(a => a.Age); /* * SELECT AVG("USERS"."AGE") AS "C" FROM "USERS" "USERS" */ ConsoleHelper.WriteLineAndReadKey(); }
public static void GroupQuery() { IQuery <User> q = context.Query <User>(); IGroupingQuery <User> g = q.Where(a => a.Id > 0).GroupBy(a => a.Age); g = g.Having(a => a.Age > 1 && AggregateFunctions.Count() > 0); g.Select(a => new { a.Age, Count = AggregateFunctions.Count(), Sum = AggregateFunctions.Sum(a.Age), Max = AggregateFunctions.Max(a.Age), Min = AggregateFunctions.Min(a.Age), Avg = AggregateFunctions.Average(a.Age) }).ToList(); /* * SELECT "USERS"."AGE" AS "AGE",COUNT(1) AS "COUNT",SUM("USERS"."AGE") AS "SUM",MAX("USERS"."AGE") AS "MAX",MIN("USERS"."AGE") AS "MIN",AVG("USERS"."AGE") AS "AVG" FROM "USERS" "USERS" WHERE "USERS"."ID" > 0 GROUP BY "USERS"."AGE" HAVING ("USERS"."AGE" > 1 AND COUNT(1) > 0) */ ConsoleHelper.WriteLineAndReadKey(); }
public static void GroupQuery() { IQuery <User> q = context.Query <User>(); IGroupingQuery <User> g = q.Where(a => a.Id > 0).GroupBy(a => a.Age); g = g.Having(a => a.Age > 1 && AggregateFunctions.Count() > 0); g.Select(a => new { a.Age, Count = AggregateFunctions.Count(), Sum = AggregateFunctions.Sum(a.Age), Max = AggregateFunctions.Max(a.Age), Min = AggregateFunctions.Min(a.Age), Avg = AggregateFunctions.Average(a.Age) }).ToList(); /* * SELECT [Users].[Age] AS [Age],COUNT(1) AS [Count],CAST(SUM([Users].[Age]) AS INT) AS [Sum],MAX([Users].[Age]) AS [Max],MIN([Users].[Age]) AS [Min],CAST(AVG([Users].[Age]) AS FLOAT) AS [Avg] FROM [Users] AS [Users] WHERE [Users].[Id] > 0 GROUP BY [Users].[Age] HAVING ([Users].[Age] > 1 AND COUNT(1) > 0) */ ConsoleHelper.WriteLineAndReadKey(); }
public static void AggregateQuery() { IQuery <User> q = context.Query <User>(); q.Select(a => AggregateFunctions.Count()).First(); /* * SELECT TOP (1) COUNT(1) AS [C] FROM [Users] AS [Users] */ q.Select(a => new { Count = AggregateFunctions.Count(), LongCount = AggregateFunctions.LongCount(), Sum = AggregateFunctions.Sum(a.Age), Max = AggregateFunctions.Max(a.Age), Min = AggregateFunctions.Min(a.Age), Average = AggregateFunctions.Average(a.Age) }).First(); /* * SELECT TOP (1) COUNT(1) AS [Count],COUNT_BIG(1) AS [LongCount],CAST(SUM([Users].[Age]) AS INT) AS [Sum],MAX([Users].[Age]) AS [Max],MIN([Users].[Age]) AS [Min],CAST(AVG([Users].[Age]) AS FLOAT) AS [Average] FROM [Users] AS [Users] */ var count = q.Count(); /* * SELECT COUNT(1) AS [C] FROM [Users] AS [Users] */ var longCount = q.LongCount(); /* * SELECT COUNT_BIG(1) AS [C] FROM [Users] AS [Users] */ var sum = q.Sum(a => a.Age); /* * SELECT CAST(SUM([Users].[Age]) AS INT) AS [C] FROM [Users] AS [Users] */ var max = q.Max(a => a.Age); /* * SELECT MAX([Users].[Age]) AS [C] FROM [Users] AS [Users] */ var min = q.Min(a => a.Age); /* * SELECT MIN([Users].[Age]) AS [C] FROM [Users] AS [Users] */ var avg = q.Average(a => a.Age); /* * SELECT CAST(AVG([Users].[Age]) AS FLOAT) AS [C] FROM [Users] AS [Users] */ ConsoleHelper.WriteLineAndReadKey(); }
public static void AggregateQuery() { IQuery <User> q = context.Query <User>(); q.Select(a => AggregateFunctions.Count()).First(); /* * SELECT COUNT(1) AS `C` FROM `Users` AS `Users` LIMIT 0,1 */ q.Select(a => new { Count = AggregateFunctions.Count(), LongCount = AggregateFunctions.LongCount(), Sum = AggregateFunctions.Sum(a.Age), Max = AggregateFunctions.Max(a.Age), Min = AggregateFunctions.Min(a.Age), Average = AggregateFunctions.Average(a.Age) }).First(); /* * SELECT COUNT(1) AS `Count`,COUNT(1) AS `LongCount`,CAST(SUM(`Users`.`Age`) AS SIGNED) AS `Sum`,MAX(`Users`.`Age`) AS `Max`,MIN(`Users`.`Age`) AS `Min`,AVG(`Users`.`Age`) AS `Average` FROM `Users` AS `Users` LIMIT 0,1 */ var count = q.Count(); /* * SELECT COUNT(1) AS `C` FROM `Users` AS `Users` */ var longCount = q.LongCount(); /* * SELECT COUNT(1) AS `C` FROM `Users` AS `Users` */ var sum = q.Sum(a => a.Age); /* * SELECT CAST(SUM(`Users`.`Age`) AS SIGNED) AS `C` FROM `Users` AS `Users` */ var max = q.Max(a => a.Age); /* * SELECT MAX(`Users`.`Age`) AS `C` FROM `Users` AS `Users` */ var min = q.Min(a => a.Age); /* * SELECT MIN(`Users`.`Age`) AS `C` FROM `Users` AS `Users` */ var avg = q.Average(a => a.Age); /* * SELECT AVG(`Users`.`Age`) AS `C` FROM `Users` AS `Users` */ ConsoleHelper.WriteLineAndReadKey(); }
public static void GroupQuery() { IQuery <User> q = context.Query <User>(); IGroupingQuery <User> g = q.Where(a => a.Id > 0).GroupBy(a => a.Age); g = g.Having(a => a.Age > 1 && AggregateFunctions.Count() > 0); g.Select(a => new { a.Age, Count = AggregateFunctions.Count(), Sum = AggregateFunctions.Sum(a.Age), Max = AggregateFunctions.Max(a.Age), Min = AggregateFunctions.Min(a.Age), Avg = AggregateFunctions.Average(a.Age) }).ToList(); /* * SELECT `Users`.`Age` AS `Age`,COUNT(1) AS `Count`,CAST(SUM(`Users`.`Age`) AS SIGNED) AS `Sum`,MAX(`Users`.`Age`) AS `Max`,MIN(`Users`.`Age`) AS `Min`,AVG(`Users`.`Age`) AS `Avg` FROM `Users` AS `Users` WHERE `Users`.`Id` > 0 GROUP BY `Users`.`Age` HAVING (`Users`.`Age` > 1 AND COUNT(1) > 0) */ ConsoleHelper.WriteLineAndReadKey(); }
public static void GroupQuery() { object ret = null; var q = context.Query <TestEntity>(); var gq = q.GroupBy(a => a.F_Int32); gq = gq.Having(a => a.F_Int32 > 1 && AggregateFunctions.Count() > 1); ret = gq.Select(a => new { a.F_Int32, Count = AggregateFunctions.Count(), Sum = AggregateFunctions.Sum(a.F_Int32), Max = AggregateFunctions.Max(a.F_Int32), Min = AggregateFunctions.Min(a.F_Int32), Avg = AggregateFunctions.Average(a.F_Int32) }).ToList(); ConsoleHelper.WriteLineAndReadKey(); }
public static void AggregateFunctionTest() { MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString); var q = context.Query <User>(); q = q.Where(a => a.Id > 0); var xxx = q.Select(a => AggregateFunctions.Count()).First(); q.Select(a => new { Count = AggregateFunctions.Count(), LongCount = AggregateFunctions.LongCount(), Sum = AggregateFunctions.Sum(a.Age), Max = AggregateFunctions.Max(a.Age), Min = AggregateFunctions.Min(a.Age), Average = AggregateFunctions.Average(a.Age) }).First(); var count = q.Count(); var longCount = q.LongCount(); var sum = q.Sum(a => a.Age); var max = q.Max(a => a.Age); var min = q.Min(a => a.Age); var avg = q.Average(a => a.Age); Console.WriteLine(1); }
public static void GroupQueryTest() { MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString); object ret = null; var q = context.Query <User>(); var r = q.GroupBy(a => a.Id).Having(a => a.Id > 1).Select(a => new { a.Id, Count = AggregateFunctions.Count(), Sum = AggregateFunctions.Sum(a.Id), Max = AggregateFunctions.Max(a.Id), Min = AggregateFunctions.Min(a.Id), Avg = AggregateFunctions.Average(a.Id) }).ToList(); q.GroupBy(a => a.Age).Having(a => a.Age > 1).Select(a => new { a.Age, Count = AggregateFunctions.Count(), Sum = AggregateFunctions.Sum(a.Age), Max = AggregateFunctions.Max(a.Age), Min = AggregateFunctions.Min(a.Age), Avg = AggregateFunctions.Average(a.Age) }).ToList(); var r1 = q.GroupBy(a => a.Age).Having(a => AggregateFunctions.Count() > 0).Select(a => new { a.Age, Count = AggregateFunctions.Count(), Sum = AggregateFunctions.Sum(a.Age), Max = AggregateFunctions.Max(a.Age), Min = AggregateFunctions.Min(a.Age), Avg = AggregateFunctions.Average(a.Age) }).ToList(); var g = q.GroupBy(a => a.Gender); //g = g.ThenBy(a => a.Name); //g = g.Having(a => a.Id > 0); //g = g.Having(a => a.Name.Length > 0); var gq = g.Select(a => new { Count = AggregateFunctions.Count() }); //gq = gq.Skip(1); //gq = gq.Take(100); //gq = gq.Where(a => a > -1); ret = gq.ToList(); var c = gq.Count(); ConsoleHelper.WriteLineAndReadKey(); }