前言
在我们实际开发场景中，报表是最常见的功能，业主爸爸们最喜欢对着大屏和报表指点江山。
今天介绍如果通过 sqlsugar 层来处理报表数据。
-
Sql可以方便的创建临时表并且可以将这些临时表进行JOIN操作。 -
Sql可以方便的实现行列互转操作。
以上2点用 sqlsugar 就能很好解决，下面我们将来展示几个最常见的实践示例。
先准备一张表，表结构如下：
实践一、按月统计没有为0
我们用 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 );
实践三、对象和表随意JOIN
比如我们创建一个 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();
实践四、 List和表随意JOIN
//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();
实践五、大数据处理
按天统计 Count，左边的表时间多了我们可以用临时表进行性能优化。
List<DateTime> dts = new List<DateTime>() { };
for(int i = 0; i < 1000; i++)//搞出所有时间断
{
dts.Add(DateTime.Now.Date.AddDays(-i));
}
//创建临时表 (用真表兼容性好，表名随机)
var tableName = "Temp" + SnowFlakeSingle.Instance.NextId();
var type = db.DynamicBuilder().CreateClass(tableName, new SugarTable())
.CreateProperty("ColumnName", typeof(DateTime), new SugarColumn() { IsPrimaryKey = true })//主键不要自增
.BuilderType();
db.CodeFirst.InitTables(type);//创建表
//将时间集合插入临时表
var insertData=dts.Select(it => new SingleColumnEntity<DateTime>() { ColumnName = it }).ToList();
db.Fastest<SingleColumnEntity<DateTime>>().AS(tableName).BulkCopy(insertData);
//查询统计
var list= db.Queryable<SingleColumnEntity<DateTime>>()
.AS(tableName)
.LeftJoin<Order>((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<DateTime> dts = new List<DateTime>() { DateTime.Now.Date.AddDays(1)};
for (int i = 0; i < 100000; i++)//搞出所有时间断
{
dts.Add(dts.Last().AddMinutes(-10));
}
dts = dts
.Where(it => it >= Convert.ToDateTime("2023-1-1"))
.Where(it => it <= Convert.ToDateTime("2023-4-7"))//过滤出你要的时间
.ToList();
var list111=db.Reportable(dts).ToQueryable<DateTime>()
.LeftJoin<Order>((it, o) => o.CreateTime >= it.ColumnName && o.CreateTime <= it.ColumnName.AddMinutes(10))
.GroupBy(it => it.ColumnName)
.Select((it,o) => new
{
it=it.ColumnName,
count=SqlFunc.AggregateCount(o.Id)
}).ToList();
实践七、 每个ID都要对应时间
List<DateTime> dts = new List<DateTime>() { 七月2号 ,七月3号 };
var IdQ= db.Queryable<Order>().Select(it => new Order()
{
Id = it.Id
}).Distinct();
var dateQ= db.Reportable(dts).ToQueryable<DateTime>();
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 的加持，感觉不要太好。好了，今天就到这吧，给我点点赞，拉拉关注。
🎀💎🎀 .***开源 ORM 框架 SqlSugar 系列 🎀💎🎀
【开篇】.***开源 ORM 框架 SqlSugar 系列
【入门必看】.***开源 ORM 框架 SqlSugar 系列
【实体配置】.***开源 ORM 框架 SqlSugar 系列
【Db First】.***开源 ORM 框架 SqlSugar 系列
【Code First】.***开源 ORM 框架 SqlSugar 系列
【数据事务】.***开源 ORM 框架 SqlSugar 系列
【连接池】.***开源 ORM 框架 SqlSugar 系列
【查询目录】.***开源 ORM 框架 SqlSugar 系列
【查询基础】.***开源 ORM 框架 SqlSugar 系列
【排序用法】.***开源 ORM 框架 SqlSugar 系列
【分组去重】.***开源 ORM 框架 SqlSugar 系列
【联表查询】.***开源 ORM 框架 SqlSugar 系列
【导航查询】.***开源 ORM 框架 SqlSugar 系列
【子查询】.***开源 ORM 框架 SqlSugar 系列
【嵌套查询】.***开源 ORM 框架 SqlSugar 系列
【配置查询】.***开源 ORM 框架 SqlSugar 系列
【并集查询】.***开源 ORM 框架 SqlSugar 系列
【树型查询】.***开源 ORM 框架 SqlSugar 系列
【表格查询】.***开源 ORM 框架 SqlSugar 系列
【动态表达式】.***开源 ORM 框架 SqlSugar 系列
【查询函数】.***开源ORM框架 SqlSugar 系列
【过滤器】.***开源 ORM 框架 SqlSugar 系列
