3.0優化查詢-引入視圖

3.0優化查詢-引入視圖

在高效雲平台下 我負責的是基礎系統的學生部分。包括對學生信息的增刪改查，學生統計部分主要是以圖表的形式展示各種信息。本篇博客主要介紹一下 從傳統的表查詢到運用視圖查詢的簡化過程。

首先，來看一下學生表關系。學生表關聯宿捨，班級，專業方向外鍵，班級裡包含專業，層次（研究生 本科 專科）。

需求

查詢學生信息：包括姓名學號，宿捨，班級，專業，專業方向等。我們可以看到後邊的這些都是其它表裡的。 有時候也會根據班級ID，專業ID等來查詢所有的學生信息。

2.0的時候，都是通過EF導航屬性來一個個查詢。但還是諸多不便。這次3.0 改成用視圖。將這些綜合信息放在了一起。查詢的時候毫不費力了。

這裡建立視圖的時候由於都是基於學生信息的，所以在表進行連接的時候應該用左連接進行。

以下是表連接語句。

SELECT     dbo.BasicClassEntities.ClassCode, dbo.BasicClassEntities.ClassName, dbo.BasicOrganizationEntities.OrganizationName, dbo.BasicOrganizationEntities.OrganizationID, 
                      dbo.BasicSchoolLevelEntities.LevelName, dbo.BasicRoomEntities.RoomName, dbo.BasicStudentEntities.StudentID, dbo.BasicStudentEntities.StudentNo, dbo.BasicStudentEntities.Name, 
                      dbo.BasicStudentEntities.UserCode, dbo.BasicStudentEntities.EntryTime, dbo.BasicStudentEntities.Origin, dbo.BasicStudentEntities.EntryPartyTime, dbo.BasicStudentEntities.Speciality, 
                      dbo.BasicStudentEntities.HealthCondition, dbo.BasicStudentEntities.ExamineeNumber, dbo.BasicStudentEntities.FatherName, dbo.BasicStudentEntities.MotherName, 
                      dbo.BasicStudentEntities.FatherPhone, dbo.BasicStudentEntities.MotherPhone, dbo.BasicStudentEntities.TrainDestination, dbo.BasicStudentEntities.Note, dbo.BasicStudentEntities.Status, 
                      dbo.BasicStudentEntities.Operator, dbo.BasicStudentEntities.TimeStamp, dbo.BasicStudentEntities.CreditCardNo, dbo.BasicStudentEntities.Sex, dbo.BasicStudentEntities.PoliticalStatus, 
                      dbo.BasicStudentEntities.PreviousName, dbo.BasicStudentEntities.Email, dbo.BasicStudentEntities.CellPhoneNumber, dbo.BasicStudentEntities.HomeTelephone, 
                      dbo.BasicStudentEntities.BirthPlace, dbo.BasicStudentEntities.HomeAddress, dbo.BasicStudentEntities.Nation, dbo.BasicStudentEntities.RoomID, dbo.BasicStudentEntities.DirectionID, 
                      dbo.BasicStudentEntities.ClassID, dbo.BasicStudentEntities.IsEnabled, dbo.BasicStudentEntities.Image, dbo.BasicDirectionEntities.DirectionID AS Expr1, dbo.BasicDirectionEntities.DirectionName, 
                      dbo.BasicDirectionEntities.DirectionCode, dbo.BasicRoomEntities.RoomID AS Expr2, dbo.BasicSchoolLevelEntities.SchoolLevelID
FROM         dbo.BasicStudentEntities LEFT OUTER JOIN
                      dbo.BasicDirectionEntities ON dbo.BasicStudentEntities.DirectionID = dbo.BasicDirectionEntities.DirectionID LEFT OUTER JOIN
                      dbo.BasicClassEntities ON dbo.BasicStudentEntities.ClassID = dbo.BasicClassEntities.ClassID LEFT OUTER JOIN
                      dbo.BasicRoomEntities ON dbo.BasicStudentEntities.RoomID = dbo.BasicRoomEntities.RoomID LEFT OUTER JOIN
                      dbo.BasicOrganizationEntities ON dbo.BasicClassEntities.OrganizationID = dbo.BasicOrganizationEntities.OrganizationID LEFT OUTER JOIN
                      dbo.BasicSchoolLevelEntities ON dbo.BasicOrganizationEntities.SchoolLevelID = dbo.BasicSchoolLevelEntities.SchoolLevelID

唯一需要注意的是 以上連接語句中 LEFT OUTER JOIN 。沒有了復雜的查詢之後，以後開發和維護都方便多了。當然最重要的不是建立了一個視圖，而是去用心的改進自己的不足。不將就是源動力。