注意:请升级到 5.0.4.7 请升级到 5.0.4.7 请升级到 5.0.4.7
用ORM实现报表有2个短板
1. Sql可以方便的 创建临时表并且可以将这些临时表进行 JOIN 操作
2.Sql可以方便的实现行列互转操作
以上2点用sqlsugar就能很好解决
表结构如下
我们用Gropby实现查询,发现结果就只有3条记录,其中2月份就没有数据,那么这个Sql就不符合要求
这个时候我们就需要生成一个月份临时表
然后上面的表在和临时表 进行JOIN 在分组,会不会认为Sql也挺麻烦的呢?
用SqlSugar轻松实现
var queryableLeft = db.Reportable(ReportableDateType.MonthsInLast1years).ToQueryable<DateTime>(); //生成月份 //ReportableDateType.MonthsInLast1yea 表式近一年月份 并且queryable之后还能在where过滤 var queryableRight = db.Queryable<operateinfo>(); //声名表 //月份和表JOIN var list=queryableLeft .LeftJoin(queryableRight,(x1,x2)=>x2.operate_time.ToString("yyyy-MM")==x1.ColumnName.ToString("yyyy-MM")) .GroupBy((x1,x2)=>x1.ColumnName) .Select((x1, x2) => new { //null的数据要为0所以不能用count count = SqlFunc.AggregateSum(SqlFunc.IIF(x2.id > 0, 1, 0)), date = x1.ColumnName.ToString("yyyy-MM") } ).ToList(); //技巧 : 近50天 //var day30= Enumerable.Range(0,50).Select(it=>DateTime.Now.Date.AddDays(it*-1)).ToList() //var queryableLeft = db.Reportable(day30).ToQueryable<DateTime>();
用Sql进行分组查询
结果只查询出1号和2号的数据,不符合要求
用SqlSugar轻松实现
//如果是查询当月那么 time就是 DateTime.Now var days = (time.AddMonths(1) - time).Days;//获取1月天数 var dayArray = Enumerable.Range(1, days).Select(it=>Convert.ToDateTime(time.ToString("yyyy-MM-"+it))).ToList();//转成时间数组 var queryableLeft = db.Reportable(dayArray).ToQueryable<DateTime>(); var queryableRight = db.Queryable<operateinfo>(); var list = db.Queryable(queryableLeft, queryableRight, JoinType.Left, (x1, x2) => x1.ColumnName.Date==x2.operate_time.Date) .GroupBy((x1, x2) => x1.ColumnName) .Select((x1, x2) => new { count = SqlFunc.AggregateSum(SqlFunc.IIF(x2.id > 0, 1, 0)), day = x1.ColumnName.Day }).ToList(); //技巧 : 近50天 //var day30= Enumerable.Range(0,50).Select(it=>DateTime.Now.Date.AddDays(it*-1)).ToList() //var queryableLeft = db.Reportable(day30).ToQueryable<DateTime>();
如果表格需要行转列,代码如下:
//如果是查询当月那么 time就是 DateTime.Now var days = (time.AddMonths(1) - time).Days;//获取1月天数 var dayArray = Enumerable.Range(1, days).Select(it=> Convert.ToDateTime(time.ToString("yyyy-MM-"+it))).ToList();//转成时间数组 var queryableLeft = db.Reportable(dayArray).ToQueryable<DateTime>(); var queryableRight = db.Queryable<operateinfo>(); var list = db.Queryable(queryableLeft, queryableRight, JoinType.Left, (x1, x2) => x1.ColumnName.Date==x2.operate_time.Date) .GroupBy((x1, x2) => x1.ColumnName) .Select((x1, x2) => new { count = SqlFunc.AggregateSum(SqlFunc.IIF(x2.id > 0, 1, 0)), day = x1.ColumnName.Day, name="合计" }).ToPivotTable(it => it.day, it => it.name, it => it.Sum(x => x.count)); //在统计时存在空引用要改成这样 //.ToPivotTable(it => it.day, it => it.name, it=> it.Any() ? it.Sum(y => y.Count):0 );
比如我们创建一个List对象,我们这个List对象就能和表进行Join了
var queryable2 = db.Reportable(List<实体>).ToQueryable(); var list= db.Queryable<Order>().InnerJoin(queryable2, (x1, x2) => x1.Id == x2.OrderId) .Select((x1, x2) => new { name = x1.Name, id = x1.Id, orderid = x2.OrderId }).ToList();
//Order join list<int> var ids = new List<int>() { 1, 2, 3 }; var queryable2 = db.Reportable(ids).ToQueryable<int>(); var list = db.Queryable<Order>().InnerJoin(queryable2, (x, y) => x.Id == y.ColumnName).ToList(); //list<int> join Order var ids = new List<int>() { 1, 2, 3 }; var list2=db.Reportable(ids).ToQueryable<int>().InnerJoin<Order>((y, x) => y.ColumnName == x.Id).ToList();
当list数据比较大时我们可以插入临时表实现
//这个是SqlServer中用法 using (db.Ado.OpenAlways())//长连接 { db.Queryable<Order>().Select(" * into #temp").ToList();//插入临时表 var tempList=db.Queryable<dynamic>().AS("#temp").ToList();//查询临时表 }
2016 © donet5.comApache Licence 2.0