设为首页收藏本站
查看: 121|回复: 0

[PHP] SqlServer2005T-SQLQuery学习笔记(1)

[复制链接]

论坛元老

Rank: 6Rank: 6

积分
34274
主题
17031
UID
1347
M币
67
贡献
17176

  • 发表于 2018-7-15 00:14:00 | 显示全部楼层 |阅读模式
    SqlServer 2005 T-SQL Query 学习笔记(1)

    Select字句在逻辑上是SQL语句最后进行处理的最后一步,所以,以下查询会发生错误:
    OrderDateOrderYearCustomerIDNumCusts dboOrders OrderYear 因为group by是在Select之前进行的,那个时候orderYear这个列并没有形成。
      
    如果要查询成功,可以像下面进行修改:
    OrderYearCustomerIDNumCusts OrderDateOrderYearCustomerID dboOrdersD OrderYear 还有一种很特殊的写法:
    OrderYearCustomerIDNumCusts OrderDateCustomerID dboOrders[U]DOrderYearCustomerID[/U]OrderYear 在作者眼里,他是非常喜欢这种写法的,因为更清晰,更明确,更便于维护。
      
    在查询中使用参数定向产生一批结果,这个技巧没有什么好说的。
      
    嵌套查询,在处理逻辑上是从里向外进行执行的。
      
    多重引用,有可能你的SQL语句包含了多次从一个表进行查询后进行连接组合。比如你要比较每年的顾客数同先前年的顾客数的变化,所以你的查询就必须JOIN了2个相同的表的实例,这也是不可避免的。
      
    Common Table Expressions (CTE)
    CTE是在SQL2005新加入的一种表的表示类型。
    它的定义如下:
    WITH cte_name
    AS
    (
    cte_query
    )
    outer_query_refferring to_cte_name;

    [color=]注意:因为在标准的T-SQL语言中已经包含了WITH关键字,所以为了区分,CTE在语句的结尾加上了“;”作为停止符。
      
    CTE实例一(结果集别名)
    C OrderDateOrderYearCustomerID dboOrders OrderYearCustomerIDNumCusts C OrderYear  
    当然,作者本人有更推荐的写法:
    COrderYearCustomerIDOrderDateCustomerID dboOrders OrderYearCustomerIDNumCusts C OrderYear  
    CTE实例二(多重CTEs)
    C1 OrderDateOrderYearCustomerID dboOrders C2 OrderYearCustomerIDNumCusts C1 OrderYear OrderYearNumCusts C2 NumCusts 70  
    CTE实例三(多重引用)
    YearlyCount OrderDateOrderYearCustomerIDNumCusts dboOrders OrderDateCurOrderYearCurNumCusts CurNumCustsPrvNumCusts PrvNumCustsCurNumCusts PrvNumCusts Growth YearlyCount Cur YearlyCount Prv CurOrderYear PrvOrderYear 1  
    CTE实例四(修改数据)
    1.把从customer表查询出来的结果,动态的组装进新表CustomersDups里:
    dboCustomersDupsGO CrossCustomers 1 cC1dboCustomers C1dboCustomers C2 cKeyColCustomerIDCompanyNameContactNameContactTitleCityRegionPostalCodeCountryPhoneFax dboCustomersDups CrossCustomers  
    2.使用CTE移除数据,只保留CustomerDups表里同一CustomerID里KeyCol为最大的记录。
    JustDups dboCustomersDups C1 KeyCol KeyColdboCustomersDups C2 C2CustomerID C1CustomerIDJustDups  
    CTE实例五(对象容器)
    即提供了封装的能力,有利于组件化的编程。作者额外的提醒,CTE无法直接内嵌,但是可以通过把CTE封装进一个对象容器里并从一个外部的CTE里对这容器的数据进行查询而实现内嵌。
      
    作者也说明了,使用CTEs在VIEW和UDFs里是没有什么价值的。
    有个例子,如下:
    dboVYearCnt YearCnt OrderDateOrderYearCustomerIDNumCusts dboOrders OrderDateYearCnt  
    CTE实例六(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 EMPEmployeeIDEMPReportsToEMPFirstNameEMPLastName EmpsCTE MGR dboEmployees EMP EMPReportsTo MGREmployeeID EmpsCTE  
    理解:一个递归的CTE包含了至少2个查询,第一个查询在CTE的身体里类似于一格锚点。这个锚点仅仅返回一个有效的表,并作为递归的一个锚。从上的例子看出来,锚点仅仅返回了一个employeeID = 5 的一行。然后的第2个查询是作为递归成员。当查询到下属成员的结果为空时,此递归结束。
      
    如果你担心递归会造成永久循环,你可以使用下面的表达:
    WITH cte_name AS (cte_body) outer_query OPTION (MAXRECURSION n);
    默认的n为100,当n=0时,无限制。
    回复

    使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    在我站开通SVIP可同时获得17个站点VIP资源 立即登录 立即注册
    快速回复 返回顶部 返回列表