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
技术分享

技术分享

定义一个变量,接受要查询的员工id,设为2,即查询2号员工下面所有的下属信息。

关于CTE的运用还有很多,这里只列出一些基本的用法,如有想法,可以提出,希望一起学习!

sqlserver学习笔记1:http://www.cnblogs.com/liupeng61624/p/4354983.html

sqlserver学习笔记2:http://www.cnblogs.com/liupeng61624/p/4367580.html

sqlserver学习笔记3:http://www.cnblogs.com/liupeng61624/p/4375135.html

sqlserver学习笔记4:http://www.cnblogs.com/liupeng61624/p/4388959.html

sqlserver学习笔记5:http://www.cnblogs.com/liupeng61624/p/4392746.html

 

希望各位大牛给出指导,不当之处虚心接受学习!谢谢!

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。