一. 分組查詢
在SQL中使用Group By 來對資料分組,在實際中分組中一般與聚合函式一併使用。在Git.Framework中提供了相應的分組方法
DataTable Group(T entity); DataTable Group(T entity, bool isOpenTrans); IEnumerable<System.Linq.IGrouping<TKey, T>> Group<TKey>(T entity, Func<T, TKey> keySelector);
對某張表進行分組查詢需要使用Group方法, 在分組的時候必須指定分組的欄位。在Entity類中有一個方法Group方法,這個方法就是用於指定分組的欄位.
AdminEntity entity = new AdminEntity(); entity.Group(a => a.CreateUser); DataTable table = this.Admin.Group(entity);
上面這段程式碼是使用CreateUser欄位對錶進行分組,返回兩個欄位: 一個CreateUser,一個是分組的行數
AdminEntity entity = new AdminEntity(); entity.Group(a => new { a.CreateUser,a.LoginCount}); DataTable table = this.Admin.Group(entity);
上面這段程式碼是根據兩個欄位分組,DataTable中返回三個欄位,由上可知.
IEnumerable<System.Linq.IGrouping<TKey, T>> Group<TKey>(T entity, Func<T, TKey> keySelector);
這是一個高大上的方法,上面返回DataTable實屬無奈,技藝當時還不精,所以只能硬生生的這麼返回值。這個泛型的方法有點意思,也有點高階了,做Linq 的都知道。沒錯這個和Linq to SQL中的效果是一樣的。返回一個Key--集合的模式.(對於動態型別目前還是升級中)
var query= this.Admin.Group(entity, a => new {a.UserCode }); foreach (var item in query) { string key=item.Key.UserCode; int count = item.Count(); }
二. 連線方法
在SQL Server中使用Left join, Right join 等關鍵字用於來連線查詢,這個對於一個碼農來說已經簡單的不能再簡單了的事情. 下面先看看一個做連結查詢的規則問題
SELECT t0.[ID],t0.[UserName],t0.[PassWord],t0.[UserCode],t0.[RealName],t0.[Email],t0.[Mobile],t0.[Phone],t0.[CreateTime],t0.[CreateIp],t0.[CreateUser],t0.[LoginCount],t0.[Picture],t0.[UpdateTime],t0.[IsDelete],t0.[Status],t0.[DepartNum],t0.[ParentCode],t0.[RoleNum],t0.[Remark],t1.[RoleName] AS RoleName FROM [dbo].[Admin] AS t0 LEFT JOIN [dbo].[SysRole] AS t1 ON t0.[RoleNum]=t1.[RoleNum]
使用關鍵字LEFT JOIN [Table] ON 條件
在Entity中提供了幾個用於連線查詢的方法.
public void Inner<T>(T entity, params Git.Framework.DataTypes.Params<string, string>[] param) where T : BaseEntity; public void Left<T>(T entity, params Git.Framework.DataTypes.Params<string, string>[] param) where T : BaseEntity; public void Right<T>(T entity, params Git.Framework.DataTypes.Params<string, string>[] param) where T : BaseEntity;
看到上面幾個方法可能有點發暈,先來解釋一下具體是怎麼回事。
T 是一個實體對映物件,就是資料庫中對映的實體類, T entity 相當於Left Join中的右表
params Git.Framework.DataTypes.Params<string, string>[] param 這個又有點奇怪了,這個是用於指定左右表的連線欄位,不明白先看看下面這個類
[Serializable] public class Params<T1> { public T1 Item1 { get; set; } public Params() { } } [Serializable] public class Params<T1, T2> { public T1 Item1 { get; set; } public T2 Item2 { get; set; } public Params() { } } [Serializable] public class Params<T1, T2, T3> { public T1 Item1 { get; set; } public T2 Item2 { get; set; } public T3 Item3 { get; set; } public Params() { } } [Serializable] public class Params<T1, T2, T3, T4> { public T1 Item1 { get; set; } public T2 Item2 { get; set; } public T3 Item3 { get; set; } public T4 Item4 { get; set; } public Params() { } } [Serializable] public class Params<T1, T2, T3, T4, T5> { public T1 Item1 { get; set; } public T2 Item2 { get; set; } public T3 Item3 { get; set; } public T4 Item4 { get; set; } public T5 Item5 { get; set; } public Params() { } } [Serializable] public class Params<T1, T2, T3, T4, T5,T6> { public T1 Item1 { get; set; } public T2 Item2 { get; set; } public T3 Item3 { get; set; } public T4 Item4 { get; set; } public T5 Item5 { get; set; } public T6 Item6 { get; set; } public Params() { } } [Serializable] public class Params<T1, T2, T3, T4, T5, T6,T7> { public T1 Item1 { get; set; } public T2 Item2 { get; set; } public T3 Item3 { get; set; } public T4 Item4 { get; set; } public T5 Item5 { get; set; } public T6 Item6 { get; set; } public T7 Item7 { get; set; } public Params() { } } [Serializable] public class Params<T1, T2, T3, T4, T5, T6,T7,T8> { public T1 Item1 { get; set; } public T2 Item2 { get; set; } public T3 Item3 { get; set; } public T4 Item4 { get; set; } public T5 Item5 { get; set; } public T6 Item6 { get; set; } public T7 Item7 { get; set; } public T8 Item8 { get; set; } public Params() { } }
很熟悉是不是,.NET4.0中有一個一樣的類,沒錯是一樣的。只不過在寫這個框架的時候還沒有這個版本,所以現在就一直保留了。
Params<string, string> 說明有兩個位string 型別的欄位,一個用於指明左表的欄位名 第二個用於指定右表的欄位名
params Git.Framework.DataTypes.Params<string, string>[] param 而這個是陣列說明可以自定多個關聯欄位 也就是on後面的條件
params 的作用是幹什麼的,不懂,自己去查。
AdminEntity entity = new AdminEntity(); entity.IncludeAll(); entity.Where(a => a.UserName == userName).And(a => a.PassWord == passWord); SysRoleEntity roleEntity = new SysRoleEntity(); roleEntity.Include("RoleName", "RoleName"); entity.Left<SysRoleEntity>(roleEntity, new Params<string, string>() { Item1 = "RoleNum", Item2 = "RoleNum" }); entity = this.Admin.GetSingle(entity);
上面的程式碼就是一個的做連結查詢案例,分別從表Admin中查詢了所有欄位,在SysRole表中查詢了欄位RoleName,並且將此欄位重新命名為RoleName,和原欄位名一樣。
SELECT t0.[ID],t0.[UserName],t0.[PassWord],t0.[UserCode],t0.[RealName],t0.[Email],t0.[Mobile],t0.[Phone],t0.[CreateTime],t0.[CreateIp],t0.[CreateUser],t0.[LoginCount],t0.[Picture],t0.[UpdateTime],t0.[IsDelete],t0.[Status],t0.[DepartNum],t0.[ParentCode],t0.[RoleNum],t0.[Remark],t1.[RoleName] AS RoleName FROM [dbo].[Admin] AS t0 LEFT JOIN [dbo].[SysRole] AS t1 ON t0.[RoleNum]=t1.[RoleNum] WHERE t0.[UserName]=@0_t0_UserName AND t0.[PassWord]=@1_t0_PassWord
重新命名的部分請檢視: t1.[RoleName] AS RoleName
LEFT JOIN [dbo].[SysRole] AS t1 ON t0.[RoleNum]=t1.[RoleNum]
做連結查詢某張表,並且使用欄位RoleNum 關聯,在Admin表中存在一個欄位RoleNum 在SysRole表中存在一個RoleNum欄位,主外來鍵關係。
new Params<string, string>() { Item1 = "RoleNum", Item2 = "RoleNum" }
這一句程式碼是連線關聯欄位的核心,指定連線查詢指定的管理欄位,如果這個傳入一個陣列那麼on 的後面將用and連線兩個表示式
上面的三個方法大同小異用法都一樣,但是要注意以下包含查詢的欄位不能重複,如果有重複欄位那麼一定要指定別名以示區分
三. 連線查詢的實質
上面專門講到了分頁查詢,上面的連線查詢都可以使用到分頁查詢,在某種程度上這種抽象還是值得稱讚的,對於物件操作層的資料和實際的資料查詢是完全隔離的,都是通過內聯的方式轉化的。
public List<AdminEntity> GetList(AdminEntity entity, ref PageInfo pageInfo) { entity.IncludeAll(); entity.OrderBy(a => a.ID, EOrderBy.DESC); entity.Where(a => a.IsDelete == (int)EIsDelete.NotDelete); SysRoleEntity roleEntity = new SysRoleEntity(); roleEntity.Include("RoleName", "RoleName"); entity.Left<SysRoleEntity>(roleEntity, new Params<string, string>() { Item1 = "RoleNum", Item2 = "RoleNum" }); SysDepartEntity departEntity = new SysDepartEntity(); departEntity.Include("DepartName", "DepartName"); entity.Left<SysDepartEntity>(departEntity, new Params<string, string>() { Item1 = "DepartNum", Item2 = "DepartNum" }); int rowCount = 0; List<AdminEntity> listResult = this.Admin.GetList(entity, pageInfo.PageSize, pageInfo.PageIndex, out rowCount); pageInfo.RowCount = rowCount; return listResult; }
程式碼看起來有點煩,但是相比ADO.NET 好像又簡化了很多。上面是一個連線查詢並且分頁的過程,看看那具體的SQL程式碼
DECLARE @StartIndex INT SET @StartIndex = 1 SELECT @RecordCount=COUNT(*) FROM [dbo].[Admin] AS t0 LEFT JOIN [dbo].[SysRole] AS t1 ON t0.[RoleNum]=t1.[RoleNum] LEFT JOIN [dbo].[SysDepart] AS t2 ON t0.[DepartNum]=t2.[DepartNum] WHERE t0.[IsDelete]=@0_t0_IsDelete IF (@PageIndex<=1) BEGIN SET @PageIndex=1 END SET @StartIndex = ( @PageIndex - 1 ) * @PageSize + 1 ; WITH TempTable AS( SELECT ROW_NUMBER() OVER (ORDER BY t0.[ID] DESC) RowNumber,t0.[ID],t0.[UserName],t0.[PassWord],t0.[UserCode],t0.[RealName],t0.[Email],t0.[Mobile],t0.[Phone],t0.[CreateTime],t0.[CreateIp],t0.[CreateUser],t0.[LoginCount],t0.[Picture],t0.[UpdateTime],t0.[IsDelete],t0.[Status],t0.[DepartNum],t0.[ParentCode],t0.[RoleNum],t0.[Remark],t1.[RoleName] AS RoleName,t2.[DepartName] AS DepartName FROM [dbo].[Admin] AS t0 LEFT JOIN [dbo].[SysRole] AS t1 ON t0.[RoleNum]=t1.[RoleNum] LEFT JOIN [dbo].[SysDepart] AS t2 ON t0.[DepartNum]=t2.[DepartNum] WHERE t0.[IsDelete]=@0_t0_IsDelete ) SELECT * FROM TempTable WHERE RowNumber BETWEEN (@StartIndex) AND (@PageIndex * @PageSize)
貌似還可以哦,完成了我們想要的東西,返回了總行數並且返回了當前頁的資料。
看一個超級複雜的連線分頁查詢。
Stopwatch watch = new Stopwatch(); watch.Reset(); watch.Start(); T_EXECUTEEntity entity = new T_EXECUTEEntity(); entity.IncludeAll(); int rowCount = 0; //計劃 T_PLANEntity plan = new T_PLANEntity(); plan.Include(a => new { PlanNumber = a.plan_number, PlanFinishQty = a.plan_finish_qty, OrderDetailID = a.order_detail_id, PlanQty = a.plan_qty }); if (!planNumber.IsEmpty()) { plan.Where("plan_number", ECondition.Like, "%" + planNumber + "%"); } entity.Left<T_PLANEntity>(plan, new Params<string, string>() { Item1 = "plan_id", Item2 = "plan_id" }); //產品 T_PARTEntity part = new T_PARTEntity(); part.Include(a => new { PartName = a.part_name, PartSpecification = a.part_specification, PartDisplay = a.part_display }); if (!PartName.IsEmpty()) { part.Where("part_name", ECondition.Like, "%" + PartName + "%"); } entity.Left<T_PARTEntity>(part, new Params<string, string>() { Item1 = "part_id", Item2 = "part_id" }); //裝置 可以不連線 T_DEVICEEntity device = new T_DEVICEEntity(); device.Include(a => new { DeviceName = a.device_name, DeviceNumber = a.device_number }); //entity.Left<T_DEVICEEntity>(device, new Params<string, string>() { Item1 = "device_id", Item2 = "device_id" }); entity.Left<T_DEVICEEntity>(device, new Params<string, string>() { Item1 = "device_number", Item2 = "device_number" }); //工序 可以不連線 T_PROCEDUREEntity procedure = new T_PROCEDUREEntity(); procedure.Include(a => new { ProcedureName = a.procedure_name }); entity.Left<T_PROCEDUREEntity>(procedure, new Params<string, string>() { Item1 = "procedure_id", Item2 = "procedure_id" }); //作業時間 T_EXECUTE_TIMEEntity entityTime = new T_EXECUTE_TIMEEntity(); entityTime.Include(a => new { ETHours = a.et_hours }); entity.Left<T_EXECUTE_TIMEEntity>(entityTime, new Params<string, string>() { Item1 = "execute_id", Item2 = "execute_id" }); if (strStatus == 0) { entity.Where("execute_end_time", ECondition.Is, null); if (!WorkGroup.IsEmpty()) { entity.And("creator", ECondition.Like, "%" + WorkGroup + "%"); } } else if (strStatus == 1) { entity.Where("execute_end_time", ECondition.IsNot, null); if (!WorkGroup.IsEmpty()) { entity.And("creator", ECondition.Like, "%" + WorkGroup + "%"); } } else { if (!WorkGroup.IsEmpty()) { entity.Where("creator", ECondition.Like, "%" + WorkGroup + "%"); } } if (!beginTime.IsEmpty() && !endTime.IsEmpty()) { DateTime begin = ConvertHelper.ToType<DateTime>(beginTime); DateTime end = ConvertHelper.ToType<DateTime>(endTime); entity.Where<T_EXECUTEEntity>("execute_start_time", ECondition.Between, begin, end); } T_ORDER_DETAILEntity orderDetail = new T_ORDER_DETAILEntity(); plan.Left<T_ORDER_DETAILEntity>(orderDetail, new Params<string, string>() { Item1 = "order_detail_id", Item2 = "order_detail_id" }); orderDetail.OrderBy(a => a.order_detail_id, EOrderBy.DESC); T_PARTEntity par = new T_PARTEntity(); par.Include(a => new { PartPictureNum = a.part_picture_number }); orderDetail.Left<T_PARTEntity>(par, new Params<string, string>() { Item1 = "part_id", Item2 = "part_id" }); if (!PicNum.IsEmpty()) { par.Where("part_picture_number", ECondition.Like, "%" + PicNum + "%"); } entity.OrderBy(a=>a.plan_id,EOrderBy.DESC); List<T_EXECUTEEntity> listResult = this.T_EXECUTE.GetList(entity, pageInfo.PageSize, pageInfo.PageIndex, out rowCount); pageInfo.RowCount = rowCount; watch.Stop(); log.Info("**************************************"+watch.ElapsedMilliseconds.ToString()); return listResult;
如果能夠理解上面這段程式碼,那麼你使用這個連線查詢絕對沒有問題,下面是生成的SQL
DECLARE @StartIndex INT SET @StartIndex = 1 SELECT @RecordCount=COUNT(*) FROM [dbo].[T_EXECUTE] AS t0 LEFT JOIN [dbo].[T_PLAN] AS t1 ON t0.[plan_id]=t1.[plan_id] LEFT JOIN [dbo].[T_ORDER_DETAIL] AS t2 ON t1.[order_detail_id]=t2.[order_detail_id] LEFT JOIN [dbo].[T_PART] AS t3 ON t2.[part_id]=t3.[part_id] LEFT JOIN [dbo].[T_PART] AS t4 ON t0.[part_id]=t4.[part_id] LEFT JOIN [dbo].[T_DEVICE] AS t5 ON t0.[device_number]=t5.[device_number] LEFT JOIN [dbo].[T_PROCEDURE] AS t6 ON t0.[procedure_id]=t6.[procedure_id] LEFT JOIN [dbo].[T_EXECUTE_TIME] AS t7 ON t0.[execute_id]=t7.[execute_id] WHERE t0.[execute_start_time] BETWEEN @0_t0_Begin_execute_start_time AND @0_t0_End_execute_start_time IF (@PageIndex<=1) BEGIN SET @PageIndex=1 END SET @StartIndex = ( @PageIndex - 1 ) * @PageSize + 1 ; WITH TempTable AS( SELECT ROW_NUMBER() OVER (ORDER BY t0.[plan_id] DESC) RowNumber,t0.[execute_id],t0.[plan_id],t0.[part_id],t0.[procedure_id],t0.[device_id],t0.[device_number],t0.[execute_start_time],t0.[execute_end_time],t0.[execute_plan_qty],t0.[execute_qty],t0.[execute_bad_qty],t0.[stuff_number],t0.[execute_state],t0.[execute_remark1],t0.[execute_remark2],t0.[creator],t0.[create_time],t0.[modifier],t0.[modify_time],t0.[execute_storage_qty],t0.[stuff_number2],t0.[stuff_number3],t0.[stuff_number4],t0.[stuff_number5],t0.[wt_code],t0.[execute_bad_qty1],t0.[execute_bad_qty2],t0.[execute_bad_qty3],t0.[execute_bad_qty4],t0.[execute_work_state],t0.[execute_restart_time],t1.[plan_number] AS PlanNumber,t1.[plan_finish_qty] AS PlanFinishQty,t1.[order_detail_id] AS OrderDetailID,t1.[plan_qty] AS PlanQty,t3.[part_picture_number] AS PartPictureNum,t4.[part_name] AS PartName,t4.[part_specification] AS PartSpecification,t4.[part_display] AS PartDisplay,t5.[device_name] AS DeviceName,t5.[device_number] AS DeviceNumber,t6.[procedure_name] AS ProcedureName,t7.[et_hours] AS ETHours FROM [dbo].[T_EXECUTE] AS t0 LEFT JOIN [dbo].[T_PLAN] AS t1 ON t0.[plan_id]=t1.[plan_id] LEFT JOIN [dbo].[T_ORDER_DETAIL] AS t2 ON t1.[order_detail_id]=t2.[order_detail_id] LEFT JOIN [dbo].[T_PART] AS t3 ON t2.[part_id]=t3.[part_id] LEFT JOIN [dbo].[T_PART] AS t4 ON t0.[part_id]=t4.[part_id] LEFT JOIN [dbo].[T_DEVICE] AS t5 ON t0.[device_number]=t5.[device_number] LEFT JOIN [dbo].[T_PROCEDURE] AS t6 ON t0.[procedure_id]=t6.[procedure_id] LEFT JOIN [dbo].[T_EXECUTE_TIME] AS t7 ON t0.[execute_id]=t7.[execute_id] WHERE t0.[execute_start_time] BETWEEN @0_t0_Begin_execute_start_time AND @0_t0_End_execute_start_time ) SELECT * FROM TempTable WHERE RowNumber BETWEEN (@StartIndex) AND (@PageIndex * @PageSize)
作者:情緣
出處:http://www.cnblogs.com/qingyuan/
關於作者:從事倉庫,生產軟體方面的開發,在專案管理以及企業經營方面尋求發展之路
版權宣告:本文版權歸作者和部落格園共有,歡迎轉載,但未經作者同意必須保留此段宣告,且在文章頁面明顯位置給出原文連結。
聯絡方式: 個人QQ 821865130 ; 倉儲技術QQ群 88718955,142050808 ;
吉特倉儲管理系統 開源地址: https://github.com/hechenqingyuan/gitwms