SqlServer 2005 T-SQL Query 学习笔记

 

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

SELECT
 YEAR(OrderDate) AS OrderYear,
 COUNT(DISTINCT CustomerID) AS NumCusts
FROM dbo.Orders
GROUP BY OrderYear;因为group by是在Select之前进行的，那个时候orderYear这个列并没有形成。

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

SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts
FROM (SELECT YEAR(OrderDate) AS OrderYear, CustomerID
   FROM dbo.Orders) AS D
GROUP BY OrderYear;还有一种很特殊的写法：

SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts
FROM (SELECT YEAR(OrderDate), CustomerID
   FROM dbo.Orders) AS D(OrderYear, CustomerID)
GROUP BY 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实例一（结果集别名）

WITH C AS
(
 SELECT YEAR(OrderDate) AS OrderYear, CustomerID
 FROM dbo.Orders
)
SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts
FROM C
GROUP BY OrderYear;
当然，作者本人有更推荐的写法：

WITH C(OrderYear, CustomerID) AS
(
 SELECT YEAR(OrderDate), CustomerID
 FROM dbo.Orders
)
SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts
FROM C
GROUP 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, NumCusts
FROM C2
WHERE NumCusts > 70;
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 Growth
FROM 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;
GO

WITH 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, Fax
INTO dbo.CustomersDups
FROM 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.VYearCnt
AS
WITH YearCnt AS
(
 SELECT YEAR(OrderDate) AS OrderYear,
  COUNT(DISTINCT CustomerID) AS NumCusts
 FROM dbo.Orders
 GROUP BY YEAR(OrderDate)
)
SELECT * FROM YearCnt;
CTE实例六（CTEs的递归）

作者给了一个例子，来讲述这个在SQL2005的新内容，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索引是SQL2005的新功能。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为100，当n=0时，无限制。

 

SQL2005增加了4个关于队计算的函数：分别是ROW_NUMBER,RANK,DENSE_RANK,NTILE.

注意：这些函数只能出现在SELECT和ORDER BY的查询中。语法如下：

ranking_function over([partition by col_list] order by col_list)

ROW_NUMBER:在排序的基础上对所有列进行连续的数字进行标识。

执行顺序：为了计算列值，优化器首先需要把数据在分区列上进行排序，然后在对这些列进行编码。

SQL2005之前的技术处理列计算

1.（SET-BASED）

在SQL2005之前，已经有了简单的对列集合的计算，使用unique partitioning + sort 组合。

比如，你可以使用下面的技术：

SELECT empid,
 (SELECT COUNT(*)
  FROM dbo.Sales AS S2
  WHERE S2.empid <= S1.empid) AS rownum
FROM dbo.Sales AS S1
ORDER BY empid;
这是非常简单的，但也是非常慢的。。。

如果需要组合条件产生列数（即非唯一列的组合排序和断路器），可以这样做：

SELECT empid, qty,
 (SELECT COUNT(*)
  FROM dbo.Sales AS S2
  WHERE S2.qty < S1.qty
   OR (S2.qty = S1.qty AND S2.empid <= S1.empid)) AS rownum
FROM dbo.Sales AS S1
ORDER BY qty, empid;
当然还有很多方法，比如用游标，就不写例子了。

2.（IDENTITY-Based Solution）

SELECT empid, qty, IDENTITY(int, 1, 1) AS rn
INTO #SalesRN FROM dbo.Sales;

SELECT * FROM #SalesRN;

DROP TABLE #SalesRN;

 

利用ROW_NUMBER()进行高效率的分页。

AD HOC PAGING：

就是指用页面的序号和页面的大小请求一个单独的页面。下面是例子。

DECLARE @pagesize AS INT, @pagenum AS INT;
SET @pagesize = 5;
SET @pagenum = 2;

WITH SalesCTE AS
(
 SELECT ROW_NUMBER() OVER(ORDER BY qty, empid) AS rownum,
  empid, mgrid, qty
 FROM dbo.Sales
)
SELECT rownum, empid, mgrid, qty
FROM SalesCTE
WHERE rownum > @pagesize * (@pagenum-1)
 AND rownum <= @pagesize * @pagenum
ORDER BY rownum;

 说明：在上个例子中，其实SQL只审视了10行（2*5），也就是说，查看N页的话，SQL只查到N的页的数据，N页后面的数据一概不查看。 另外，每当移动一页，都会把这页放进缓存里，因此每次查询，就是逻辑查询（缓存）+物理查询的过程。物理查询只需要查询新请求的页即可，其他全部在缓存里执行，这样大大加快了查询速度。  MULTIPAGE ACCESS：

如果结果集不是很大，而且分了多个请求页面，请求也不向前移动，那么这是一个好的方案：首先在一个表里使用ROW_NUMBER具体化所有的页，然后创建一个群集索引。下面是例子。
首先创建按ROW_NUMBER把列编好，

SELECT ROW_NUMBER() OVER(ORDER BY qty, empid) AS rownum, empid, mgrid, qty INTO #SalesRN FROM dbo.Sales;

CREATE UNIQUE CLUSTERED INDEX idx_rn ON #SalesRN(rownum);

然后直接按ROWNUM查询，

DECLARE @pagesize AS INT, @pagenum AS INT; SET @pagesize = 5; SET @pagenum = 2;
SELECT rownum, empid, mgrid, qty FROM #SalesRN WHERE rownum BETWEEN @pagesize *
(@pagenum-1) + 1 AND @pagesize * @pagenum ORDER BY rownum;

RANK & DENSE RANK

这2个函数和ROW_NUMBER的区别是：ROW_NUMBER在ORDER BY的条件里有重复行存在的话，是把这些重复行也按INDEX排列的，但是RANK和DENSE RANK总是确定的，即只要是ORDER BY重复的行，他们是统一INDEX的。

RANK和DENSE_RANK的区别是，RANK是如果上级的INDEX和下级的INDEX有可能不是+1关系，是按下级真正处于列里的位置进行INDEX，而DENSE_RANK是按照跟上级的INDEX+1的关系进行的编码。

比如：

SELECT empid, qty, RANK() OVER(ORDER BY qty) AS rnk, DENSE_RANK() OVER(ORDER BY qty) AS drnk FROM dbo.Sales ORDER BY qty;

NTILE

NTILE的用法和其他的RANK函数一样，只不过它可以传入一个参数，用来决定最大的INDEX是多少：它会按行数进行除法，然后平均分配行数进行INDEX的标示。

比如，如果有11列，那么首先11/3=3，3列一组作为一个INDEX，然后，11%3=2，这2列会分别加在前面的2组上。

比如,

SELECT empid, qty,
 CASE NTILE(3) OVER(ORDER BY qty, empid)
  WHEN 1 THEN 'low'
  WHEN 2 THEN 'medium'
  WHEN 3 THEN 'high'
 END AS lvl
FROM dbo.Sales
ORDER BY qty, empid;

作者他很喜欢建立数字辅助表（即是1-N的数字按顺序组成的表），关于如何建立这些辅助表，然后他给了一些例子，这些例子很有代表性

比如，我要建立一个1，000，000行的数字表：

CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 1000000;
SET @rc = 1;

INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
END

INSERT INTO dbo.Nums
SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;

 

这种方式非常巧妙，它并不是一个一个的循环插入，而是一次插入很多行，{1}，{2}，{3，4}，{5，6，7，8}。。。

 

为什么这样会快呢？

是因为它节省了跟比较其他可用解决方案进行比较和记录这些日志的时间。

 

然后，作者给了一个CTE的递归的解决方案:

DECLARE @n AS BIGINT;
SET @n = 1000000;

WITH Nums AS
(
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM Nums WHERE n < @n
)
SELECT n FROM Nums
OPTION(MAXRECURSION 0);--为了移除默认100的递归限制

有个更优的CTE的解决方案，就是先生成很多行，然后用ROW_NUMBER进行计算，再选择ROW_NUMBER这列的值就可以了。

复制代码 代码如下:
DECLARE @n AS BIGINT;
SET @n = 1000000;

WITH Base AS
(
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM Base WHERE n < CEILING(SQRT(@n))
),
Expand AS
(
SELECT 1 AS c
FROM Base AS B1, Base AS B2
),
Nums AS
(
SELECT ROW_NUMBER() OVER(ORDER BY c) AS n
FROM Expand
)
SELECT n FROM Nums WHERE n <= @n
OPTION(MAXRECURSION 0);

利用笛卡尔积进行不断的累加，达到了22n行。

最后，作者给出了一个函数，用于生成这样的数字表：
复制代码 代码如下:
CREATE FUNCTION dbo.fn_nums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT n FROM Nums WHERE n <= @n;
GO