3.0优化查询-引入视图

在高效云平台下 我负责的是基础系统的学生部分。包括对学生信息的增删改查，学生统计部分主要是以图表的形式展示各种信息。本篇博客主要介绍一下 从传统的表查询到运用视图查询的简化过程。

首先，来看一下学生表关系。学生表关联宿舍，班级，专业方向外键，班级里包含专业，层次（研究生 本科 专科）。

       

需求

    查询学生信息：包括姓名学号，宿舍，班级，专业，专业方向等。我们可以看到后边的这些都是其它表里的。 有时候也会根据班级ID，专业ID等来查询所有的学生信息。

2.0的时候，都是通过EF导航属性来一个个查询。但还是诸多不便。这次3.0 改成用视图。将这些综合信息放在了一起。查询的时候毫不费力了。

这里建立视图的时候由于都是基于学生信息的，所以在表进行连接的时候应该用左连接进行。

以下是表连接语句。

  

<span style="font-size:18px;">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</span>

 唯一需要注意的是 以上连接语句中 LEFT OUTER JOIN   。没有了复杂的查询之后，以后开发和维护都方便多了。当然最重要的不是建立了一个视图，而是去用心的改进自己的不足。不将就是源动力。