Lambda表达式之查询篇
https://www.cnblogs.com/netlws/p/9490871.html
lambda表达式多条件查询
https://www.cnblogs.com/wy1992/p/6904442.html
C# LINQ语法详解
https://www.cnblogs.com/sxjljj/p/11348652.html
CTE 递归查询全解
https://www.cnblogs.com/ljhdo/p/4580347.html
CTE在Oracle和Sqlserver中使用的差异
https://blog.csdn.net/zouqingfang/article/details/9771681
http://vlambda.com/wz_5lBZKpN7dM7.html
如何在EF中实现left join(左联接)查询
https://www.cnblogs.com/beyond1983/p/5743445.html
Linq分组及排序,取前N条记录
https://blog.csdn.net/lqh4188/article/details/51444094
https://blog.csdn.net/make1828/article/details/54632786
Linq日期差值计算,Linq日期比较方法
https://blog.csdn.net/ningxi_/article/details/72828741
C# 递归构造树状数据结构(泛型),如何构造?如何查询?
https://www.cnblogs.com/chenwolong/p/asWith.html
WITH org("Id","No","Name","ParentId","LeaderId","Leader","Comment","Sort","OrgTypeId","OrgType","CreateUser","CreateTime","UpdateUser","UpdateTime") AS ( SELECT "Id","No","Name","ParentId","LeaderId","Leader","Comment","Sort","OrgTypeId","OrgType","CreateUser","CreateTime","UpdateUser","UpdateTime" FROM "ZTHROrganization" WHERE "Id"='20191008-1736-2676-7000-2608445F6318' UNION ALL SELECT z."Id",z."No",z."Name",z."ParentId",z."LeaderId",z."Leader",z."Comment",z."Sort",z."OrgTypeId",z."OrgType", z."CreateUser",z."CreateTime",z."UpdateUser",z."UpdateTime" FROM "ZTHROrganization" z INNER JOIN org o ON z."ParentId" = o."Id" ) SELECT * FROM org ORDER BY "ParentId"
public static void QueryCTESub(string id) { using (var db = Chaolj.DAL.ZTHR.ZTHRContext.Create()) { string cteSql = @"WITH org(""Id"",""No"",""Name"",""ParentId"",""LeaderId"",""Leader"",""Comment"",""Sort"",""OrgTypeId"",""OrgType"",""CreateUser"",""CreateTime"",""UpdateUser"",""UpdateTime"") AS " +"(select " +@"""Id"",""No"",""Name"",""ParentId"",""LeaderId"",""Leader"",""Comment"",""Sort"",""OrgTypeId"",""OrgType"",""CreateUser"",""CreateTime"",""UpdateUser"",""UpdateTime"" FROM ""ZTHROrganization"" WHERE ""Id""='20191008-1736-2676-7000-2608445F6318' " +@"UNION ALL " +@"SELECT z.""Id"",z.""No"",z.""Name"",z.""ParentId"",z.""LeaderId"",z.""Leader"",z.""Comment"",z.""Sort"",z.""OrgTypeId"",z.""OrgType"",z.""CreateUser"",z.""CreateTime"",z.""UpdateUser"",z.""UpdateTime"" FROM ""ZTHROrganization"" z " +@"INNER JOIN org o ON z.""ParentId"" = o.""Id"")" +@"SELECT * FROM org ORDER BY ""ParentId"""; string ctesql = @"WITH org AS( SELECT * FROM ""ZTHROrganization"")SELECT * FROM org"; var list = db.ZTHROrganization.SqlQuery(cteSql).ToList(); } }
分组多字段求和
query.GroupBy(q => new { q.Year, q.Month }) .Select(q => new { Year = q.Key.Year, Month = q.Key.Month, BuildAmount = q.Sum(i => i.BuildAmount), RecAmount = q.Sum(i => i.RecAmount), Amount = q.Sum(i => i.Amount), RealAmount = q.Sum(i => i.RealAmount) });
不分组多字段求和(这样得到的就是对应字段的总的求和,其实还是利用了分组,不过给分组依据传个空,如果利用linq的话就是传个常数)
where.GroupBy(x => new { }).Select(q => new { sumWeight = q.Sum(x => x.Weight), sumQuantity = q.Sum(x => x.Quantity), sumIncome = q.Sum(x => x.Income) }).FirstOrDefault();
分组求和IQueryable
var materialModel = from m in qry group m by new { m.ItemSubClass } into g select new { ItemSubClass = g.Key.ItemSubClass, Amount = g.Sum(m => m.Amount), Qty = g.Sum(m => m.Qty) };
IQueryable.Join方法
model = db.ZTHREmployee.Join(db.ZTHREmployeeContacts, e => e.Id, c => c.EmployeeId, (e, c) => new { e.Id, e.ArchivesNo, e.CreateTime, e.CreateUser, e.Files, e.IDCard, e.IDCardVerifier, e.IDCardVerifierId, e.InsurancePaymentPlace, e.LeaveDate, e.LeaveReasons, e.LeaveType, e.LeaveTypeId, e.Name, e.No, e.PositiveTime, e.RecruitmentPlace, e.RecruitmentWay, e.RemainAnnualLeave, e.SocialSecurityNo, e.StartingTime, e.Status, e.Type, e.TypeId, e.UpdateTime, e.UpdateUser, e.WorkPlace, c.EMail, c.EmployeeId, c.FirstContactPhone, c.FirstEmergencyContact, c.HomePhone, ContactsId = c.Id, c.MobileNo, c.ParentAddress, c.ParentalContact, c.PresentAddress, c.SecondContactPhone, c.SecondEmergencyContact }).Where(p => p.Id == id).FirstOrDefault(); }
EF使用Group By 时生成的sql语句会产生APPLY语法,但Oracle11 c 不支持,此时只有使用sql语句解决
Oracle关于如何获取分组排序后的第一条数据
--方式1 SELECT t1.*,t2.nums FROM "PS_QualityMargin" t1, ( SELECT b."ProjectNo",b."SupplierNo", COUNT(*) nums, MAX("Date") "dt" FROM "PS_QualityMargin" b GROUP BY b."ProjectNo",b."SupplierNo" ) t2 WHERE t1."ProjectNo" = t2."ProjectNo" AND t1."Date" = t2."dt"; --方式2 SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER(PARTITION BY "ProjectNo","SupplierNo" ORDER BY "Date" DESC) rn,COUNT(*) OVER(PARTITION BY "ProjectNo","SupplierNo") nums FROM "PS_QualityMargin" t ) WHERE rn = 1; ---推荐使用方式2,row_number()是比rownum更强大的伪列。
Linq 分组(group by)求和(sum)并且按照分隔符(join)分割列数据
https://www.cnblogs.com/zq281660880/archive/2012/09/26/2704836.html
用符号分隔
var query = from c in t.AsEnumerable() group c by new { pingming = c.Field<string>("品名"), guige = c.Field<string>("规格") } into s select new { pingming = s.Select(p => p.Field<string>("品名")).First(), shuliang = s.Sum(p => Convert.ToInt32(p.Field<string>("数量"))), guige = s.Select(p => p.Field<string>("规格")).First(), biaohao = string.Join(";",s.Select(p => p.Field<string>("表号"))) }; DataTable tbl = tableA1.Clone(); query.ToList().ForEach(p => tbl.Rows.Add(p.pingming, p.guige,p.biaohao,p.shuliang));