SqlServer 2005 T-SQL Query 学习笔记（1)

编辑：rootadmin

Select字句在逻辑上是SQL语句最后进行处理的最后一步，所以，以下查询会发生错误：

SELECT YEAR(OrderDate) AS OrderYear, COUNT(DISTINCT CustomerID) AS NumCustsFROM dbo.OrdersGROUP BY OrderYear;

因为group by是在Select之前进行的，那个时候orderYear这个列并没有形成。

如果要查询成功，可以像下面进行修改：

SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCustsFROM (SELECT YEAR(OrderDate) AS OrderYear, CustomerID FROM dbo.Orders) AS DGROUP BY OrderYear;

还有一种很特殊的写法：

SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCustsFROM (SELECT YEAR(OrderDate), CustomerID FROM dbo.Orders) AS D(OrderYear, CustomerID)GROUP BY OrderYear;

在作者眼里，他是非常喜欢这种写法的，因为更清晰，更明确，更便于维护。

在查询中使用参数定向产生一批结果，这个技巧没有什么好说的。

嵌套查询，在处理逻辑上是从里向外进行执行的。

多重引用，有可能你的SQL语句包含了多次从一个表进行查询后进行连接组合。比如你要比较每年的顾客数同先前年的顾客数的变化，所以你的查询就必须JOIN了2个相同的表的实例，这也是不可避免的。

Common Table Expressions (CTE)

CTE是在SQL新加入的一种表的表示类型。

它的定义如下：

WITH cte_name

(

cte_query

)

outer_query_refferring to_cte_name;

注意：因为在标准的T-SQL语言中已经包含了WITH关键字，所以为了区分，CTE在语句的结尾加上了“;”作为停止符。

CTE实例一（结果集别名）

WITH C AS( SELECT YEAR(OrderDate) AS OrderYear, CustomerID FROM dbo.Orders)SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCustsFROM CGROUP BY OrderYear;

当然，作者本人有更推荐的写法：

WITH C(OrderYear, CustomerID) AS( SELECT YEAR(OrderDate), CustomerID FROM dbo.Orders)SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCustsFROM CGROUP BY OrderYear;

CTE实例二（多重CTEs）

WITH C1 AS( SELECT YEAR(OrderDate) AS OrderYear, CustomerID FROM dbo.Orders),C2 AS( SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts FROM C1 GROUP BY OrderYear)SELECT OrderYear, NumCustsFROM C2WHERE NumCusts > ;

CTE实例三（多重引用）

WITH YearlyCount AS( SELECT YEAR(OrderDate) AS OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts FROM dbo.Orders GROUP BY YEAR(OrderDate))SELECT Cur.OrderYear, Cur.NumCusts AS CurNumCusts, Prv.NumCusts AS PrvNumCusts, Cur.NumCusts - Prv.NumCusts AS GrowthFROM YearlyCount AS Cur LEFT OUTER JOIN YearlyCount AS Prv ON Cur.OrderYear = Prv.OrderYear + 1;

CTE实例四（修改数据）

1.把从customer表查询出来的结果，动态的组装进新表CustomersDups里：

IF OBJECT_ID('dbo.CustomersDups') IS NOT NULL DROP TABLE dbo.CustomersDups;GOWITH CrossCustomers AS( SELECT 1 AS c, C1.* FROM dbo.Customers AS C1, dbo.Customers AS C2)SELECT ROW_NUMBER() OVER(ORDER BY c) AS KeyCol, CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, FaxINTO dbo.CustomersDupsFROM CrossCustomers;

2.使用CTE移除数据，只保留CustomerDups表里同一CustomerID里KeyCol为最大的记录。

WITH JustDups AS( SELECT * FROM dbo.CustomersDups AS C1 WHERE KeyCol < (SELECT MAX(KeyCol) FROM dbo.CustomersDups AS C2 WHERE C2.CustomerID = C1.CustomerID))DELETE FROM JustDups;

CTE实例五（对象容器）

即提供了封装的能力，有利于组件化的编程。作者额外的提醒，CTE无法直接内嵌，但是可以通过把CTE封装进一个对象容器里并从一个外部的CTE里对这容器的数据进行查询而实现内嵌。

作者也说明了，使用CTEs在VIEW和UDFs里是没有什么价值的。

有个例子，如下:

CREATE VIEW dbo.VYearCntASWITH YearCnt AS( SELECT YEAR(OrderDate) AS OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts FROM dbo.Orders GROUP BY YEAR(OrderDate))SELECT * FROM YearCnt;

CTE实例六（CTEs的递归）

作者给了一个例子，来讲述这个在SQL的新内容，CTEs的递归。

根据employeeId，返回此员工的信息，并包含所有下级员工的信息。（等级关系基于empolyeeId和reportsTo的属性）所返回的结果包含下列字段，employeeId,reportsTo,FirstName,LastName。

作者在这里，给予了一个最佳的索引方式：

CREATE UNIQUE INDEX idx_mgr_emp_ifname_ilname ON dbo.Employees(ReportsTo, EmployeeID) INCLUDE(FirstName, LastName);

作者的解释：这个索引将通过一个单独的查询（局部扫描）来取得每个经理的直接下级。Include（FristName,LastName）加在这里，即是覆盖列。

小知识：什么Include索引？

Include索引是SQL的新功能。Include索引的列并不影响索引行的物理存储顺序，他们作为一个挂件‘挂在'索引行上。挂这些‘挂件'的目的在于，只需要扫描一把索引就获得了这些附加数据。

回到作者的例子上，下面是递归的代码：

WITH EmpsCTE AS( SELECT EmployeeID, ReportsTo, FirstName, LastName FROM dbo.Employees WHERE EmployeeID = 5 UNION ALL SELECT EMP.EmployeeID, EMP.ReportsTo, EMP.FirstName, EMP.LastName FROM EmpsCTE AS MGR JOIN dbo.Employees AS EMP ON EMP.ReportsTo = MGR.EmployeeID)SELECT * FROM EmpsCTE;

理解：一个递归的CTE包含了至少2个查询，第一个查询在CTE的身体里类似于一格锚点。这个锚点仅仅返回一个有效的表，并作为递归的一个锚。从上的例子看出来，锚点仅仅返回了一个employeeID = 5 的一行。然后的第2个查询是作为递归成员。当查询到下属成员的结果为空时，此递归结束。

如果你担心递归会造成永久循环，你可以使用下面的表达：

WITH cte_name AS (cte_body) outer_query OPTION (MAXRECURSION n);

默认的n为，当n=0时，无限制。

推荐整理分享SqlServer 2005 T-SQL Query 学习笔记（1)，希望有所帮助，仅作参考，欢迎阅读内容。

文章相关热门搜索词:,内容如对您有帮助，希望把文章链接给更多的朋友！

SqlServer T-SQL Query 学习笔记（2） SQL增加了4个关于队计算的函数：分别是ROW_NUMBER,RANK,DENSE_RANK,NTILE.注意：这些函数只能出现在SELECT和ORDERBY的查询中。语法如下：ranking_functionover([partiti

SqlServer T-SQL Query 学习笔记（3） ADHOCPAGING：就是指用页面的序号和页面的大小请求一个单独的页面。下面是例子。DECLARE@pagesizeASINT,@pagenumASINT;SET@pagesize=5;SET@pagenum=2;WITHSalesCTEAS(SELECTROW_NUM

SqlServer T-SQL Query 学习笔记（4）比如，我要建立一个1，，行的数字表：CREATETABLEdbo.Nums(nINTNOTNULLPRIMARYKEY);DECLARE@maxASINT,@rcASINT;SET@max=;SET@rc=1;INSERTINTONumsVALUES(1);WHILE@rc*2=@maxBEGININSE

标签: SqlServer 2005 T-SQL Query 学习笔记（1)

本文链接地址:https://www.jiuchutong.com/biancheng/319296.html 转载请保留说明！

上一篇：关于MSSQL 2005的安装与补丁(ms sql 2012)

下一篇：SqlServer 2005 T-SQL Query 学习笔记（2）