SqlServer 2005 T-SQL Query 学习笔记（1)
|
Select字句在逻辑上是SQL语句最后进行处理的最后一步，所以，以下查询会发生错误： OrderDateOrderYearCustomerIDNumCusts dboOrders OrderYear因为group by是在Select之前进行的，那个时候orderYear这个列并没有形成。 如果要查询成功，可以像下面进行修改： OrderYearCustomerIDNumCusts OrderDateOrderYearCustomerID dboOrdersD OrderYear还有一种很特殊的写法： OrderYearCustomerIDNumCusts OrderDateCustomerID dboOrdersDOrderYearCustomerID) OrderYear在作者眼里，他是非常喜欢这种写法的，因为更清晰，更明确，更便于维护。 在查询中使用参数定向产生一批结果，这个技巧没有什么好说的。 嵌套查询，在处理逻辑上是从里向外进行执行的。 多重引用，有可能你的SQL语句包含了多次从一个表进行查询后进行连接组合。比如你要比较每年的顾客数同先前年的顾客数的变化，所以你的查询就必须JOIN了2个相同的表的实例，这也是不可避免的。 Common Table Expressions (CTE)CTE是在SQL2005新加入的一种表的表示类型。 它的定义如下： WITH cte_name AS ( cte_query ) outer_query_refferring to_cte_name; 注意：因为在标准的T-SQL语言中已经包含了WITH关键字，所以为了区分，CTE在语句的结尾加上了“;”作为停止符。 CTE实例一（结果集别名） C OrderDateOrderYearCustomerID dboOrders OrderYearCustomerIDNumCusts C OrderYear当然，作者本人有更推荐的写法： COrderYearCustomerIDOrderDateCustomerIDNumCusts C OrderYearCTE实例二（多重CTEs） C1 OrderDateOrderYearCustomerID dboOrders C2 OrderYearCustomerIDNumCusts C1 OrderYear OrderYearNumCusts C2 NumCusts 70CTE实例三（多重引用） YearlyCount OrderDateOrderYearCustomerIDNumCusts dboOrders OrderDateCurOrderYearCurNumCusts CurNumCustsPrvNumCusts PrvNumCustsCurNumCusts PrvNumCusts Growth YearlyCount Cur YearlyCount Prv CurOrderYear PrvOrderYear 1CTE实例四（修改数据） 1.把从customer表查询出来的结果，动态的组装进新表CustomersDups里： dboCustomersDupsGO<SPAN style="COLOR: blue">WITH CrossCustomers <SPAN style="COLOR: blue">AS<SPAN style="COLOR: gray">( <SPAN style="COLOR: blue">SELECT 1 <SPAN style="COLOR: blue">AS c<SPAN style="COLOR: gray">,C1<SPAN style="COLOR: gray">.* <SPAN style="COLOR: blue">FROM dbo<SPAN style="COLOR: gray">.Customers <SPAN style="COLOR: blue">AS C1<SPAN style="COLOR: gray">,dbo<SPAN style="COLOR: gray">.Customers <SPAN style="COLOR: blue">AS C2 <SPAN style="COLOR: gray">) <SPAN style="COLOR: blue">SELECT <SPAN style="COLOR: magenta">ROW_NUMBER<SPAN style="COLOR: gray">() <SPAN style="COLOR: blue">OVER<SPAN style="COLOR: gray">(<SPAN style="COLOR: blue">ORDER BY c<SPAN style="COLOR: gray">) <SPAN style="COLOR: blue">AS KeyCol<SPAN style="COLOR: gray">,CustomerID<SPAN style="COLOR: gray">,CompanyName<SPAN style="COLOR: gray">,ContactName<SPAN style="COLOR: gray">,ContactTitle<SPAN style="COLOR: gray">,<SPAN style="COLOR: blue">Address<SPAN style="COLOR: gray">,City<SPAN style="COLOR: gray">,Region<SPAN style="COLOR: gray">,PostalCode<SPAN style="COLOR: gray">,Country<SPAN style="COLOR: gray">,Phone<SPAN style="COLOR: gray">,Fax <SPAN style="COLOR: blue">INTO dbo<SPAN style="COLOR: gray">.CustomersDups <SPAN style="COLOR: blue">FROM CrossCustomers<SPAN style="COLOR: gray">; 2.使用CTE移除数据，只保留CustomerDups表里同一CustomerID里KeyCol为最大的记录。 JustDups dboCustomersDups C1 KeyCol KeyColdboCustomersDups C2 C2CustomerID C1CustomerIDJustDupsCTE实例五（对象容器） 即提供了封装的能力，有利于组件化的编程。作者额外的提醒，CTE无法直接内嵌，但是可以通过把CTE封装进一个对象容器里并从一个外部的CTE里对这容器的数据进行查询而实现内嵌。 作者也说明了，使用CTEs在VIEW和UDFs里是没有什么价值的。 有个例子，如下: dboVYearCnt YearCnt OrderDateOrderYearCustomerIDNumCusts dboOrders OrderDateYearCntCTE实例六（CTEs的递归） 作者给了一个例子，来讲述这个在SQL2005的新内容，CTEs的递归。 根据employeeId，返回此员工的信息，并包含所有下级员工的信息。（等级关系基于empolyeeId和reportsTo的属性）所返回的结果包含下列字段，employeeId,reportsTo,FirstName,LastName。 作者在这里，给予了一个最佳的索引方式： idx_mgr_emp_ifname_ilname dboEmployeesReportsToEmployeeIDFirstNameLastName作者的解释： 这个索引将通过一个单独的查询（局部扫描）来取得每个经理的直接下级。Include（FristName,LastName）加在这里，即是覆盖列。 小知识：什么Include索引？ Include索引是SQL2005的新功能。Include索引的列并不影响索引行的物理存储顺序，他们作为一个挂件‘挂在'索引行上。挂这些‘挂件'的目的在于，只需要扫描一把索引就获得了这些附加数据。 回到作者的例子上，下面是递归的代码： EmpsCTE EmployeeIDReportsToFirstNameLastName dboEmployees EmployeeID 5 SELECT EMP<SPAN style="COLOR: gray">.EmployeeID<SPAN style="COLOR: gray">,EMP<SPAN style="COLOR: gray">.ReportsTo<SPAN style="COLOR: gray">,EMP<SPAN style="COLOR: gray">.FirstName<SPAN style="COLOR: gray">,EMP<SPAN style="COLOR: gray">.LastName<SPAN style="COLOR: blue">FROM EmpsCTE <SPAN style="COLOR: blue">AS MGR <SPAN style="COLOR: gray">JOIN dbo<SPAN style="COLOR: gray">.Employees <SPAN style="COLOR: blue">AS EMP <SPAN style="COLOR: blue">ON EMP<SPAN style="COLOR: gray">.ReportsTo <SPAN style="COLOR: gray">= MGR<SPAN style="COLOR: gray">.EmployeeID <SPAN style="COLOR: gray">) <SPAN style="COLOR: blue">SELECT <SPAN style="COLOR: gray">* <SPAN style="COLOR: blue">FROM EmpsCTE<SPAN style="COLOR: gray">; 理解：一个递归的CTE包含了至少2个查询，第一个查询在CTE的身体里类似于一格锚点。这个锚点仅仅返回一个有效的表，并作为递归的一个锚。从上的例子看出来，锚点仅仅返回了一个employeeID = 5 的一行。然后的第2个查询是作为递归成员。当查询到下属成员的结果为空时，此递归结束。 如果你担心递归会造成永久循环，你可以使用下面的表达： WITH cte_name AS (cte_body) outer_query OPTION (MAXRECURSION n); 默认的n为100，当n=0时，无限制。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |