写点什么

union 分页 /group/join 复杂查询 (.net core/framework)

用户头像
Spook
关注
发布于: 刚刚

union 分页/group/join 复杂查询(.net core/framework)

unoin 是一个比较特殊的查询,对 union 进行分页,关联,分组需要在最外面包装一层,如果对 union 结果再进行其它关联,分组,复杂度直线上升,解决此问题


  1. 安装 nuget 包:CRL

  2. using CRL;


以下为默认数据源实现


如果使用 ef core 和 ado.net 见:Data/EFTest · hubroxxl/CRL - 码云 - 开源中国 (gitee.com)


定义数据源


var builder = DBConfigRegister.GetInstance();builder.RegisterDBAccessBuild(dbLocation =>        {            return new DBAccessBuild(DBType.MSSQL, "server=.;database=testDb; uid=sa;pwd=123;");        });
复制代码


定义对象管理器


public class ProductRepository:BaseProvider<ProductData>{    public static ProductRepository Instance        {            get { return  new ProductRepository(); }        }}
复制代码


通过 GetLambdaQuery 方法创建 ILambdaQuery


ILambdaQuery 能实现子查询和嵌套查询,只要符合 T-SQL 语义逻辑,可以使用 ILambdaQueryResultSelect 无限叠加


如:


  • join 后 group

  • join 后再 join

  • group 后再 join

  • join 一个 group 结果

  • join 一个 union 结果

  • 对 union 进行 group 再 join

  • ...


简单的 union


var query = ProductRepository.Instance.GetLambdaQuery().Where(b => b.Id < 200);            var query2 = query.CreateQuery<Code.Order>().Where(b => b.Id < 200);            var view1 = query.Select(b => new { a1 = b.Id, a2 = b.ProductName });            var view2 = query2.Select(b => new { a1 = b.Id, a2 = b.Remark });            var result = view1.Union(view2).OrderBy(b => b.a1).OrderBy(b => b.a2, false).ToList();            var sql = query.PrintQuery();
复制代码


生成 SQL 为


select t1.[Id] as a1,t1.[ProductName1] as a2 from [ProductData] t1  with (nolock)  where (t1.[Id]<'200') union allselect t2.[Id] as a1,t2.[Remark] as a2 from [OrderProduct] t2  with (nolock)  where (t2.[Id]<'200')  order by [a1] desc,[a2] asc
复制代码


对 union 进行分页


var query = ProductRepository.Instance.GetLambdaQuery().Where(b => b.Id < 200);            query.Take(10);            var query2 = query.CreateQuery<Code.Order>().Where(b => b.Id < 200).Take(5);            var view1 = query.Select(b => new { a1 = b.Id, a2 = b.ProductName });            var view2 = query2.Select(b => new { a1 = b.Id, a2 = b.Remark });            var union = view1.Union(view2).OrderBy(b => b.a1).OrderBy(b => b.a2, false);            union.UnionPage(15, 1);//分页参数            var result = union.ToList();            var sql = query.PrintQuery();
复制代码


生成 SQL 为


SELECT * FROM (select a1,a2,ROW_NUMBER() OVER ( Order by [a1] desc,[a2] asc ) AS RowNumber  from (select top 10 t1.[Id] as a1,t1.[ProductName1] as a2 from [ProductData] t1  with (nolock)  where (t1.[Id]<200) union all select top 5 t2.[Id] as a1,t2.[Remark] as a2 from [OrderProduct] t2  with (nolock)  where (t2.[Id]<200)) tu) T WHERE T.RowNumber BETWEEN 1 AND 15 order by RowNumber
复制代码


union 后再 join


var query = ProductRepository.Instance.GetLambdaQuery().Where(b => b.Id < 200);            query.Take(10);            var query2 = query.CreateQuery<Code.Order>().Where(b => b.Id < 200).Take(5);            var view1 = query.Select(b => new { a1 = b.Id, a2 = b.ProductName });            var view2 = query2.Select(b => new { a1 = b.Id, a2 = b.Remark });            var union = view2.Union(view1).OrderBy(b => b.a1).OrderBy(b => b.a2, false);            var join = query.Join(union, (a, b) => a.Id == b.a1).Select((a, b) => new { a.Id, b.a2 });//join            var result = join.ToList();            var sql = query.PrintQuery();
复制代码


生成 SQL 为


select top 10 t1.[Id],t3.[a2] as a2 from [ProductData] t1  with (nolock)  Inner join (select top 5 t2.[Id] as a1,t2.[Remark] as a2 from [OrderProduct] t2  with (nolock)  where (t2.[Id]<'200') union all select top 10 t1.[Id] as a1,t1.[ProductName1] as a2 from [ProductData] t1  with (nolock)  where (t1.[Id]<'200')  order by [a1] desc,[a2] asc) t3  on t1.[Id]=t3.a1 where (t1.[Id]<'200')
复制代码


union 后再 group


var query = ProductRepository.Instance.GetLambdaQuery().Where(b => b.Id < 200);            var query2 = query.CreateQuery<Code.Order>().Where(b => b.Id < 200);            var view1 = query.Select(b => new { a1 = b.Id, a2 = b.ProductName });            var view2 = query2.Select(b => new { a1 = b.Id, a2 = b.Remark });            var union = view1.Union(view2).OrderBy(b => b.a2, false);            var group = union.UnionGroupBy(b => b.a2);//group            var result = group.Select(b => new { b.a2 }).ToList();            var sql = query.PrintQuery();
复制代码


生成 SQL 为


select a2 from (select t1.[Id] as a1,t1.[ProductName1] as a2 from [ProductData] t1  with (nolock)  where (t1.[Id]<'200') union allselect t2.[Id] as a1,t2.[Remark] as a2 from [OrderProduct] t2  with (nolock)  where (t2.[Id]<'200')) tu group by a2  order by [a2] asc
复制代码


源码示例参考


Data/QueryTest/test · hubroxxl/CRL - 码云 - 开源中国 (gitee.com)

用户头像

Spook

关注

还未添加个人签名 2021.06.24 加入

还未添加个人简介

评论

发布
暂无评论
union 分页/group/join 复杂查询(.net core/framework)