【报表查询】.NET开源ORM框架 SqlSugar 系列

06-01 1464阅读

文章目录

前言
实践一、按月统计没有为0
实践二、统计某月每天的数量
实践三、对象和表随意JOIN
实践四、 List和表随意JOIN
实践五、大数据处理
实践六、每10分钟统计Count
实践七、每个ID都要对应时间

总结

前言

在我们实际开发场景中，报表是最常见的功能，业主爸爸们最喜欢对着大屏和报表指点江山。

今天介绍如果通过 sqlsugar 层来处理报表数据。

Sql 可以方便的创建临时表并且可以将这些临时表进行 JOIN 操作。
Sql 可以方便的实现行列互转操作。

以上2点用 sqlsugar 就能很好解决，下面我们将来展示几个最常见的实践示例。

先准备一张表，表结构如下：

实践一、按月统计没有为0

我们用 Gropby 实现查询，发现结果就只有3条记录，其中2月份就没有数据，那么这个 Sql 就不符合要求。

这个时候我们就需要生成一个月份临时表：

然后上面的表在和临时表进行 JOIN 再分组，会不会认为 Sql 也挺麻烦的呢？

💯用 SqlSugar 轻松实现：

var queryableLeft = db.Reportable(ReportableDateType.MonthsInLast1years).ToQueryable(); //生成月份
//ReportableDateType.MonthsInLast1yea 表式近一年月份 并且queryable之后还能在where过滤
var queryableRight = db.Queryable(); //声名表
 
//月份和表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();

实践二、统计某月每天的数量

用 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();
var queryableRight = db.Queryable();
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();

如果表格需要行转列，代码如下：

//如果是查询当月那么 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();
var queryableRight = db.Queryable();
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　);

实践三、对象和表随意JOIN

比如我们创建一个 List 对象，我们这个 List 对象就能和表进行 Join 了

  var queryable2 = db.Reportable(List).ToQueryable();
   var list= db.Queryable().InnerJoin(queryable2, (x1, x2) => x1.Id == x2.OrderId)
       .Select((x1, x2) => new { name = x1.Name, id = x1.Id, orderid = x2.OrderId }).ToList();

实践四、 List和表随意JOIN

//Order join list
 var ids = new List() { 1, 2, 3  };
 var queryable2 = db.Reportable(ids).ToQueryable();
 var list = db.Queryable().InnerJoin(queryable2, (x, y) => x.Id == y.ColumnName).ToList();
 
 
 //list  join  Order 
 var ids = new List() { 1, 2, 3 };
 var list2=db.Reportable(ids).ToQueryable().InnerJoin((y, x) => y.ColumnName == x.Id).ToList();

实践五、大数据处理

按天统计 Count，左边的表时间多了我们可以用临时表进行性能优化。

   
List dts = new List() { };
for(int i = 0; i  new SingleColumnEntity() { ColumnName = it }).ToList();
db.Fastest().AS(tableName).BulkCopy(insertData);
 
 
//查询统计
var list= db.Queryable()
                .AS(tableName)
                .LeftJoin((it, o) => o.CreateTime.Date==it.ColumnName.Date)
                .GroupBy(it => it.ColumnName)
                .Select((it, o) => new
                {
                    it = it.ColumnName,
                    count = SqlFunc.AggregateCount(o.Id)
                }).ToList();
                 
//删除临时表
db.DbMaintenance.DropTable(tableName);

实践六、每10分钟统计Count

List dts = new List() { DateTime.Now.Date.AddDays(1)};
            for (int i = 0; i  it >= Convert.ToDateTime("2023-1-1"))
                .Where(it => it  o.CreateTime >= it.ColumnName && o.CreateTime  it.ColumnName)
                .Select((it,o) => new
                {
                    it=it.ColumnName,
                    count=SqlFunc.AggregateCount(o.Id)
                }).ToList();

实践七、每个ID都要对应时间

         List dts = new List() { 七月2号 ,七月3号 }; 
 
            var IdQ= db.Queryable().Select(it => new Order()
            {
                Id = it.Id
            }).Distinct();
 
            var dateQ= db.Reportable(dts).ToQueryable();
 
 
            var newlist= IdQ.InnerJoin(dateQ, (x, y) => true)
                .Select((x,y)=>new { 
                  id=x.Id,
                  time=y.ColumnName
                })
                .MergeTable()
                //leftjoin 
                .OrderBy(it=>it.time).ToList();

总结

以前用原生 SQL 写报表查询真的太费劲了，现在有了 ORM 的加持，感觉不要太好。好了，今天就到这吧，给我点点赞，拉拉关注。

🎀💎🎀 .NET开源 ORM 框架 SqlSugar 系列 🎀💎🎀

【开篇】.NET开源 ORM 框架 SqlSugar 系列

【入门必看】.NET开源 ORM 框架 SqlSugar 系列

【实体配置】.NET开源 ORM 框架 SqlSugar 系列

【Db First】.NET开源 ORM 框架 SqlSugar 系列

【Code First】.NET开源 ORM 框架 SqlSugar 系列

【数据事务】.NET开源 ORM 框架 SqlSugar 系列

【连接池】.NET开源 ORM 框架 SqlSugar 系列

【查询目录】.NET开源 ORM 框架 SqlSugar 系列

【查询基础】.NET开源 ORM 框架 SqlSugar 系列

【排序用法】.NET开源 ORM 框架 SqlSugar 系列

【分组去重】.NET开源 ORM 框架 SqlSugar 系列

【联表查询】.NET开源 ORM 框架 SqlSugar 系列

【导航查询】.NET开源 ORM 框架 SqlSugar 系列

【子查询】.NET开源 ORM 框架 SqlSugar 系列

【嵌套查询】.NET开源 ORM 框架 SqlSugar 系列

【配置查询】.NET开源 ORM 框架 SqlSugar 系列

【并集查询】.NET开源 ORM 框架 SqlSugar 系列

【树型查询】.NET开源 ORM 框架 SqlSugar 系列

【表格查询】.NET开源 ORM 框架 SqlSugar 系列

【动态表达式】.NET开源 ORM 框架 SqlSugar 系列

【查询函数】.NET开源ORM框架 SqlSugar 系列

【过滤器】.NET开源 ORM 框架 SqlSugar 系列

免责声明：我们致力于保护作者版权，注重分享，被刊用文章因无法核实真实出处，未能及时与作者取得联系，或有版权异议的，请联系管理员，我们会立即处理! 部分文章是来自自研大数据AI进行生成,内容摘自(百度百科,百度知道,头条百科,中国民法典,刑法,牛津词典,新华词典,汉语词典,国家院校,科普平台)等数据,内容仅供学习参考,不准确地方联系删除处理! 图片声明：本站部分配图来自人工智能系统AI生成,觅知网授权图片,PxHere摄影无版权图库和百度，360，搜狗等多加搜索引擎自动关键词搜索配图，如有侵权的图片，请第一时间联系我们。