SQLServer学习笔记系列6

一.写在前面的话

时间是我们每个人都特别熟悉的，但是到底它是什么，用什么来衡量，可能很多人会愣在那里。时间可以见证一切，也可以消磨一切，那些过往的点点滴滴可思可忆。回想往年清明节过后，在家乡的晚上总能听见阵阵的青蛙叫声，那是清脆的叫声，那是家乡的味道。时间一转眼，貌似那些日子已离我远去好久，在城市的喧嚣浮华中，找寻不到那种内心的宁静。感叹时间流逝的同时，怀念过去的点点滴滴。我想在繁华的都市中寻找一种安定的心情来学习，或许是一种不错的方式。学习才会让我们认清自己，找回自我，做内心的强者，不骄不躁，积极进取，阳光自信。努力的人最幸运！Just do it！

从今以后，别再过你应该过的人生，去过你想过的人生吧！——梭罗

二.派生表

派生表也就是特殊的子查询，用在from之后的子查询，同时这里注意一点：派生表必须起别名。

例如：加入我们要查询，顾客信息，以及每个国家所拥有的顾客数量。那么我们可以这样写：

1  
2  SELECT * FROM
3  (
4  SELECT custid,COUNT(*) OVER(PARTITION BY country) AS N'顾客数量'
5  FROM Sales.Customers
6  ) t

括号里面的子句就相当于一个派生表，假如我们在这里不给 COUNT(*) OVER(PARTITION BY country) 起别名，那么select * 就不知道取出哪一列，所以会报错。

同时有另一种起别名的方法，也就是在外面其别名，同样可以达到效果，写法如下：

1  SELECT * FROM
2  (
3  SELECT custid,COUNT(*) OVER(PARTITION BY country)
4  FROM Sales.Customers
5  ) t(custid,顾客数量)

三.CTE(公用表表达式)

（1）常见CTE用法

如何使用CTE，在这里必须先定义,并且命名。定义形式如下：

1  WITH USE_Customers
2  AS
3  (
4    SELECT companyname,country
5    FROM Sales.Customers 
6    WHERE country='USA'
7  )

那么我们要使用CTE，只要查询对应的CTE名称即可。

1  WITH USE_Customers
2  AS
3  (
4    SELECT companyname,country
5    FROM Sales.Customers 
6    WHERE country='USA'
7  )
8  
9  SELECT * FROM USE_Customers

结果如图所示：

但是要注意的一点是：使用CTE时，外部查询一旦完成，那么CTE的生命期就结束了，如果需要在查询这个CTE，此时失效。

同时也可以给CTE每列其别名，其中也存在在表达式内部和外部起别名，看个人喜欢哪种方式。

1  WITH USE_Customers
2  AS
3  (
4    SELECT companyname AS N'公司名',country AS N'国家名'
5    FROM Sales.Customers 
6    WHERE country='USA'
7  )
8  
9  SELECT * FROM USE_Customers

1  WITH USE_Customers(公司名,国家名)
2  AS
3  (
4    SELECT companyname ,country 
5    FROM Sales.Customers 
6    WHERE country='USA'
7  )
8  
9  SELECT * FROM USE_Customers

其查询结果都为：

（2）有参数的CTE

使用有参数的CTE可以多次使用，只需要改变参数的传递值即可。

例如：查询每个国家的顾客信息，国家存在USA，UK........，多个国家，此时我们将国家作为一个变量，类似于C#中的参数。声明CTE如下：

 1  DECLARE @country NVARCHAR(300);
 2  SET @country='USA';
 3  WITH USE_Customers(公司名,国家名)
 4  AS
 5  (
 6    SELECT companyname ,country 
 7    FROM Sales.Customers 
 8    WHERE country=@country
 9  )
10  
11  SELECT * FROM USE_Customers

假如要查询UK国家的一些顾客信息，那么只需要改变set @country=‘UK’就可以查询了，不需要改变CTE的结构。

 1  DECLARE @country NVARCHAR(300);
 2  SET @country='UK';
 3  WITH USE_Customers(公司名,国家名)
 4  AS
 5  (
 6    SELECT companyname ,country 
 7    FROM Sales.Customers 
 8    WHERE country=@country
 9  )
10  
11  SELECT * FROM USE_Customers

（3）复杂的CTE使用

【1】在这里，假如我们要查询年度订单数量在10以上的顾客信息。按照一般的思维，我们可以讲订单按照年度分组，然后用聚合函数count统计每个顾客下的订单数量，接着找出大于订单数量大于10的顾客信息。所以我们可以下sql如下：

查询结果如图所示：

【2】那么同时可以使用子查询，即派生表依次来出来问题。

第一步，先查询订单的年份，顾客的信息

1  SELECT  YEAR(orderdate) AS orderyear,custid
2  FROM Sales.Orders

第二步，根据年份进行分组，统计每位顾客的订单数量。第一步查询出来的结果作为子查询，即派生表。

1  SELECT orderyear,custid,COUNT(*) AS N'订单数量'
2  FROM
3  (
4      SELECT  YEAR(orderdate) AS orderyear,custid 
5      FROM Sales.Orders
6  ) AS t1
7  GROUP BY orderyear,custid

第三步，找出订单数量大于10的顾客信息。

 1  SELECT orderyear,custid,ordercount
 2  FROM
 3  (
 4  SELECT orderyear,custid,COUNT(*) AS  ordercount
 5  FROM
 6          (
 7              SELECT  YEAR(orderdate) AS orderyear,custid 
 8              FROM Sales.Orders
 9          ) AS t1
10          GROUP BY orderyear,custid
11  ) AS t2
12  WHERE ordercount >10

是不是感觉使用子查询，一层套一层，不过分析以后，符合我们一般解决问题的思路先做哪一步，再做哪一步。在这里我们可以使用新学的CTE来解决问题。

【3】使用CTE来解决年度订单数量在10以上的顾客信息。

好处就是定义好的CTE，可以直接拿来作为一个派生表使用，那么我们可以这样定义CTE，按照上述的分析，分别定义三个CTE，最后查询出结果：

需要注意的一点事:定义多个CTE之间用逗号（,）隔开即可，不需要使用with重新定。

 1  WITH yearorder01
 2  AS
 3  (
 4        SELECT YEAR(orderdate) AS orderyear,custid
 5        FROM Sales.Orders
 6  ),
 7  yearorder02
 8  AS 
 9  (
10          SELECT orderyear,custid,COUNT(*) AS ordercount
11          FROM yearorder01
12          GROUP BY orderyear,custid
13  ),
14  yearorder03
15  AS
16  (
17         SELECT orderyear,custid,ordercount
18         FROM yearorder02
19         WHERE  ordercount>10
20  )
21  
22  
23  SELECT * FROM yearorder03

查询结果如图所示，跟子查询结果相同：

四.多CTE

多个CTE的使用会让我们更加方便的进行相关子查询。例如：假如我们要查询年度有多少顾客，测试该怎么处理了？我们会想到，还是根据年度分组，然后聚合count（custid）求出每年度有多少顾客，其中要注意去重，即相同的custid属于同一个顾客，所以要写成count（distinct custid）。sql如下：

1  
2  SELECT YEAR(orderdate) AS orderyear,COUNT(DISTINCT custid) AS  custcount
3  FROM Sales.Orders
4  GROUP BY YEAR(orderdate)

现在加入有一个需求，就是要做一个查询，求出当年的顾客数量以及前一年的顾客数量，同时算出两者之间顾客数量隔了多少，我们又该怎么处理了？要求前一年和当前年的差，那么就可以想到，是否可以把当前查出来的结果集做一次连接，不就可以得到前一年和当前年的嘛，所以sql如下：

 1  SELECT pre_orderyear,now_orderyear,pre_custcount,now_custcount,
 2        (now_custcount-pre_custcount) AS N'顾客数量差'
 3  FROM 
 4  (
 5  SELECT YEAR(orderdate) AS now_orderyear,COUNT(DISTINCT custid) AS now_custcount 
 6  FROM Sales.Orders
 7  GROUP BY YEAR(orderdate)
 8  ) AS t1
 9  LEFT JOIN 
10  (
11   SELECT YEAR(orderdate) AS pre_orderyear,COUNT(DISTINCT custid) AS  pre_custcount
12  FROM Sales.Orders
13  GROUP BY YEAR(orderdate)
14  ) AS t2
15  ON t1.now_orderyear=t2.pre_orderyear+1;

其中就是将结果集做一个自身的连接，但是我们是不是发现这样写显得很冗余了，作为程序猿的我们就是要写出尽量简洁明了的code，Don‘t repeat yourself ！在这里我们的CTE就发挥出他的作用，定义的CTE可以在下一步中使用，作为查询条件。那么我们看看用CTE如何实现？同理，我们使用CTE之前就必须先定义CTE，这里我们定义一个CTE，查询出每年的顾客数量，其实跟上面的sql一样，只不过定义成CTE的形式。

 1  WITH custcount
 2  AS
 3  (
 4  SELECT YEAR(orderdate) AS orderyear,COUNT(DISTINCT custid) AS  custcount
 5  FROM Sales.Orders
 6  GROUP BY YEAR(orderdate)
 7  )
 8  
 9  SELECT t1.orderyear AS nowYear,t2.orderyear AS preYear,t1.custcount AS nowcount,t2.custcount AS precount,
10        (t1.custcount-t2.custcount) AS N'顾客数量差'
11  FROM custcount t1
12  LEFT JOIN  custcount t2
13  ON t1.orderyear=t2.orderyear+1;

其结果如图所示：

其实我们从sql语句可以看出，CTE的简洁之处，就在于，可以将已定义的CTE重复使用进行连接，相比于子查询自身的连接，更加简洁，容易操作，加入需要的计算，我们只需要改变CTE的定义即可。

五.递归CTE

递归的CTE在递归查找中应用的非常多，其实可以看做一种层次的查找关系，比如公司一般情况下是分一级部门，二级部门，三级部门.......那么一级部门下包含多个二级部门，二级部门又包含多个三级部门等等.....,举例说明：公司内部有上下级的关系，假如我们要查找某个员工的上级是谁，一般情况下我们可以做一次自身的连接，找出上级是谁。

例如：查找所有顾客的上级信息。也就是在HR.employees里面的mgrid等于员工的empid，那么找到的那个员工几位mgrid的上司。sql如下：

1  SELECT t1.empid,t1.mgrid,t1.lastname,t2.empid,t2.lastname
2  FROM HR.Employees t1 LEFT JOIN hr.Employees t2 
3  ON t1.mgrid =t2.empid

因为为了确保所有的员工信息都显示，所以在这里用到饿左连接，保证左边表里面的员工都存在。其结果如图：

这里再进一步讨论，加入我们要查询某个员工下面所有的下属信息，怎么办了？比如要查询2号员工的下属信息，我们这可以这样写sql：

1  SELECT * FROM 
2  hr.Employees WHERE mgrid=2

我们了可以看到2号员工下属有3号和5号，那么5号员工可能也有下属，接着查询：

1  SELECT * FROM HR.Employees
2  WHERE mgrid in
3  (SELECT empid FROM 
4  hr.Employees WHERE mgrid=2
5  )

我们可以看到，查出的结果中有4，6,7,8,9，那么4，6,7,8,9下面是否还有下属了，是不是还需要接着查询了，这样查下去就是无止尽的，因为我们不知道有多少层，所以递归CTE就是解决这样的问题的，递归CTE可以查询出所以层级的情况，直至结束。首先递归CTE需要定义一个起点，即查询起点。然后根据定义的CTE自身做递归查询，直至找出所有的节点信息。如下：

 1 DECLARE @mgrid INT;
 2 SET @mgrid=2;
 3 WITH Emplist
 4 AS
 5 (
 6     --此处为起点，执行一次
 7     SELECT empid,lastname,mgrid
 8     FROM HR.Employees
 9     WHERE mgrid=@mgrid
10     UNION ALL
11     
12     --递归开始
13     
14     SELECT e.empid,e.lastname,e.mgrid
15     FROM HR.Employees e INNER JOIN Emplist m
16     ON e.mgrid=m.empid 
17     
18 )
19 
20 SELECT * FROM Emplist