那些年我们写过的T-SQL(中篇)

中篇的重点在于,在复杂情况下使用表表达式的查询,尤其是公用表表达式(CTE),也就是非常方便的WITH AS XXX的应用,在SQL代码,这种方式至少可以提高一倍的工作效率。此外开窗函数ROW_NUMBER的使用也使得数据库分页变得异常的容易,其他的一些特性使用相对较少,在需要时再查阅即可。

本系列包含上中下三篇,内容比较驳杂,望大家耐心阅读:

那些年我们写过的T-SQL(上篇):上篇介绍查询的基础,包括基本查询的逻辑顺序、联接和子查询

那些年我们写过的T-SQL(中篇):中篇介绍表表达式、集合运算符和开窗函数

那些年我们写过的T-SQL(下篇):下篇介绍数据修改、事务&并发和可编程对象

表表达式Table Expression是一种命名的查询表达式,代表一个有效的关系表与其他表的使用类似。SQL Server支持4种类型的表表达式:派生表、公用表表达式、视图等。

  • 派生表

派生表也称为子查询表,非常的常见,之前介绍相关子查询时那些命名了的外部表均是表表达式。表表达式并没有任何的物理实例化,其优势在于使得代码逻辑清晰并可重用,但对性能并无影响。

获取处理订单数超过100的订单年度及其客户数量:SELECT * FROM (SELECT orderyear, COUNT(DISTINCT custid)) AS numcusts             FROM (SELECT YEAR(orderdate) AS orderyear, custid FROM sales.[order]) AS D1 GROUP BY orderyear) AS D2 WHERE numcusts > 100

  • 公用表表达式CTE

其是T-SQL提供的一种表表达式的增强形式,使用起来非常的便捷方便(重用性很强),z而且代码非常的清晰,在数据库查询分页等场景下和开窗函数ROW_NUMBER()配合的很好,这儿将之前介绍的派生表转化为CTE的形式。

嵌套的CTE

WITH D1 AS ( SELECT YEAR(orderdate) AS orderyear, custid FROM sales.[order] GROUP BY orderyear ), D2 AS( SELECT orderyear, COUNT(DISTINCT custid)) AS numcusts FROM D1 ) SELECT * FROM D2 WHERE numcusts > 70

递归的CTE

这个比较有意思,比如想在员工表中获取当前雇员的最大BOSS时很有效哦 WITH empsCTE AS( SELECT * FROM hr.employee WHERE empid = 6 --定位点元素 UNION ALL SELECT c.* FROM empsCTE AS p JOIN hr.employee AS c ON c.empid = p.manageid --递归元素 ) SELECT * FROM empsCTE WHERE manageid IS NULL

  • 视图和内嵌表值函数(参数化视图)

视图

IF OBJECT_ID('sale.ChinaCusts') IS NOT NULL DROP VIEW sale.ChinaCusts GO CREATE VIEW sale.ChinaCusts AS SELECT * FROM sale.Customer WHERE country = 'China'

内嵌表值函数

IF OBJECT_ID('dbo.GetOrderByUID') IS NOT NULL DROP FUNCTION dbo.GetOrderByUID GO CREATE FUNCTION dbo.GetOrderByUID (@uid AS INT) RETURNS TABLE AS RETURN SELECT * FROM sales.[order] WHERE uid = @uid; GO SELECT * FROM dbo.GetOrderByUID(8888) AS O;

  • APPLY操作符

该运算符也是一个表运算符,其支持CROSS APPLY和OUTER APPLY两种类型。其对两个输入表进行操作,右侧表往往是是一个派生表或者内联的TVF。其逻辑查询处理阶段将右侧表应用到左侧表的每一行,并生成组合的结果集。它与JOIN操作符最大的不同是右侧的表可以引用左侧表中的属性,例子如下。

返回每个客户3个最近的订单: SELECT c.custid, a.orderid, a.orderdate FROM sales.customer as c CROSS[OUTER] APPLY     (SELECT TOP(3) orderid, empid, orderdate, requiredate FROM sales.[order] AS o WHERE o.custid = c.custid ORDER BY orderdate DESC, orderid DESC) AS a

当使用CROSS APPLY操作符时会将orderid为空列去除,而OUTER APPLY则会在第二个逻辑阶段把其添加上,和外联接操作类似。

T-SQL支持集合运算符,除了常见UNION还支持INTERSECT和EXCEPT,也就是并集、交集和差集,其优先级顺序是INTERSECT > UNION = EXCEPT。需要注意的一点是,集合操作符默认认为两个NULL值是相等的,而不是之前逻辑操作符中提到的UNKNOWN。可能你会说使用外联接或者EXISTS运算符也可以达到相似效果,并在存在NULL比较的情况下必须添加相应处理代码,使用集合操作符可以简化SQL代码。

集合操作默认都存在一个隐式去除重复(即包含DISDINCT)的行为,只有UNION ALL支持重复数据。这儿补充一个关于集合概念,集合指不包含重复数据的集合,包含重复数据的情况我们称之为多元集合。在对两个(或多个)查询结果集进行集合操作时,需要注意其中的查询并不支持ORDER BY操作,如果还是需要这样的功能可以使用外部的ORDER BY或者是使用TOP等操作符将返回的游标转化为结果集。

集合操作符涉及的查询应该有相同列数,并对应列具有兼容类型(即低级别数据可以隐式的转化为高级别数据,如int->bigint),查询的列名称由第一次查询决定(在其中设置列别名)。

元数据查询类型

解释与示例

UNION [ALL], INTERSECT, EXCEPT

SELECT country, region, city FROM address UNION SELECT country, region, city FROM user order by country

复杂情况

对前置查询进行复杂操作,获取1、6号员工最近的2个订单,使用表表达式: SELECT empid, orderid, orderdate FROM (SELECT TOP 2 empid, orderid, orderdate FROM [order] WHERE empid = 1 ORDER BY orderdate DESC) AS O1 UNION ALL SELECT empid, orderid, orderdate FROM (SELECT TOP 2 empid, orderid, orderdate FROM [order] WHERE empid = 6 ORDER BY orderdate DESC) AS O2

INTERSECT[EXCEPT] ALL的替代方案

实际SQL SERVER还不支持这种类型的操作,理解起来有点复杂,简单来说就是如果我的子查询A, B都有重复数据,一个是3条,一个是5条, 那么其INTERSECT ALL操作结果应该为3条,EXCEPT ALL的结果是2条。代码如下,重点是熟悉开窗函数的使用。 SELECT row_number() OVER(PARTITION BY country, region, city ORDER BY (SELECT 0)) AS rownum, country, region, city FROM address INTERSECT SELECT row_number() OVER(PARTITION BY country, region, city ORDER BY (SELECT 0)) AS rownum, country, region, city FROM user 这儿注意的是ORDER BY (SELECT 0)的用法,表示告诉系统不用排序的意思,减少不必要的开销。

这部分内容主要涉及T-SQL自身的一些新特性,例如开窗函数、透视数据等概念,相对来说比以前的内容难理解一些,不过经常几次简单的实践,你会发现它的强大和有效。

  • 开窗函数

其根据基础查询的行子集计算,为子集中每行计算一个标量结果值,行子集被称为"窗口",通过OVER字句进行相关操作,简单来说以前对分组查询操作GROUP BY的粒度仅限于一个聚合函数(子查询操作也类似),比如SUM(Amount),但现在想对分组内的行记录进行排序,这个更小的操作粒度在过去的SQL中是难以实现的,这是开窗函数却可以完成这部分的工作。常见的分组查询实际在查询中定义集合或组,因此在查询中的所有计算都要在这些组中完成,还记得那个逻辑顺序吧,GROUP BY是在SELECT之前的,因此一旦分组后,自然的就丢失了很多细节信息,但现在开窗函数是在SELECT字句阶段,那么也就是说所有的信息仍然都在,可以支持各种细粒度的操作。此外,开窗函数能够定义顺序,并不会和显示数据时的排序混淆。

计算每个雇员每月的销售总计值:SELECT empid, ordermonth, val, SUM(val) OVER (PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS runval FROM Sales.EmpOrders

以上的窗口函数包括三个部分:分区、排序和框架。

分区字句,PARTITION BY:限定聚合函数运算的行子集,比如这个用empid分区,那么每个窗口自会包含该empid的计算(类似一个分组子集)。

顺序字句,ORDER BY:定义窗口中的排序,但不要和显示排序混淆,窗口排序是针对之后的窗口框架的,无论如何不要忘记字句的逻辑处理顺序,外部的ORDER BY字句是在SELECT字句后的。

框架字句,ROWS BETWEEN <top delimiter> AND <bottom delimiter>:进一步筛选之前的行子集(类似在子集中使用TOP操作),这儿的UNBOUNDED PRECEDING表示分区开始,CURRENT ROW表示当前行,使用UNBOUNDED FOLLOWING表示分区中的最后一行。

接下来介绍三类开窗函数,其中排序和聚合使用的场景比较多。

开窗函数类型

解释与示例

排名开窗函数

其中包含4种类型的排名函数,ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE(),最常用的是ROW_NUMBER,介绍一个分页场景 WITH CTE AS( SELECT ROW_NUMBER() OVER(ORDER BY custid) AS rownum, * FROM Sales.Customers) SELECT * FROM CTE WHERE rownum > 10 AND rownum <= 20 接下来介绍一个分区内排序,要求选取每个雇员最大的3单金额及其排名 WITH CTE AS( SELECT ROW_NUMBER() OVER(PARTITION BY empid ORDER BY freight DESC) AS rownum_ingroup, * FROM Sales.Orders) SELECT empid, freight, rownum_ingroup FROM CTE WHERE rownum_ingroup >= 1 AND rownum_ingroup <= 3

偏移开窗函数

涉及LAG、LEAD、FIRST_VALUE、LAST_VALUE四个函数,这儿就介绍LEG和LEAD,表示当前记录的前一个记录和后一个记录,记得在上篇的子查询有写过一种"小于该值的最大值"的方式,这儿使用函数更加的简单。 SELECT orderid, freight, LAG(freight) OVER(ORDER BY orderid) AS pre_freight, LEAD(freight) OVER(ORDER BY orderid) AS next_freight FROM Sales.Orders 这儿比较奇葩的是LAG用于获取前一条记录,LEAD获取后一条记录,不得不说设计的小伙伴那天"脑袋不小心被门夹了下",哈哈

聚合开窗函数

看到之后的例子,你会感觉开窗函数和人类的自然语言很像,获取每个订单、所有订单的运费总和 SELECT orderid, freight, SUM(freight) OVER() AS freightTotal FROM Sales.Orders

  • 透视和逆透视数据

透视实际上就是常说的"行转列",而逆透视就是常说的"列转行",由于这种操作实际上已有标准SQL的解决方案,不过很复杂和繁琐,这儿将SQL标准的解决方案和PIVOT、UNPIVOT函数的解决方案都描述出来。

透视/逆透视解决方案

解释与示例

标准透视

相信大家都很熟悉这种写法,因为面试中经常问到 SELECT empid, SUM(CASE WHEN custid = 'A' THEN qty END) AS A,      SUM(CASE WHEN custid = 'B' THEN qty END) AS B,      SUM(CASE WHEN custid = 'C' THEN qty END) AS C,      SUM(CASE WHEN custid = 'D' THEN qty END) AS D FROM dbo.orders GROUP BY empid; 这儿需要强调的重点是这个解决方案其实涉及3个阶段:第一个阶段为GROUP BY empid分组阶段;第二阶段为扩展阶段通过在SELECT字句中使用针对目标列的CASE表达式;最后一个阶段聚合阶段通过对每个CASE表达式结果聚合,例如SUM。

PIVOT透视

PIVOT实际是一个表运算符,包含分组、扩展、聚合三个逻辑阶段 SELECT empid, A, B, C, D FROM ( SELECT empid, custid, qty FROM dbo.Orders) AS D PIVOT(SUM(qty) FOR custid IN (A, B, C, D)) AS P 以上可以发现子查询D中,包含empid、custid、qty三个属性,其中custid作为分组属性,qty作为聚合属性,那么剩下的empid就是扩展属性(不显示的指出但可以推算出)

标准逆透视

WITH CTE AS( SELECT empid, custid, CASE custid WHEN 'A' THEN A WHEN 'B' THEN B WHEN 'C' THEN C END AS qty FROM dbo.EmpCustOrders CROSS JOIN (VALUES('A'), ('B'), ('C'), ('D')) AS Custs(custid) ) SELECT * FROM CTE WHERE qty IS NOT NULL 逆透视包括也包括三个逻辑阶段:第一阶段需要通过交叉联接生成每一列对应的一个副本;第二阶段通过CASE运算符生成列(qty);最后一个阶段通过去qty IS NOT NULL删除不相关的交叉点,这一点一定不能忘了。

UNPIVOT逆透视

SELECT empid, custid, qty FROM dbo.EmpCustOrders UNPIVOT(qty FOR custid IN(A, B, C, D)) AS U ,有没有觉得超简单?

  • 分组集

分组集就是一个属性集,分组GROUP BY字句只支持在一个查询中使用一种分组方式,如果需要多种分组的结果就需要通过UNION ALL将多个分组聚合起来,为了字段对应,需要为部分列设置NULL占位符。这部分的使用场景主要是在报表分析中,分组集提供4类操作符用于增强原有的GROUP BY字句,这儿就介绍GROUPING SETS操作符,CUBE和ROLLUP是对它的简化,可以通过语义理解,CUBE是立方即包含提供的分组属性的所有组合,ROLLUP是归纳,按照层次对分组属性进行组合,最后的GROUPING和GROUPING_ID是对分组的标识。

GROUPING SETS

SELECT empid, custid, SUM(qty) AS sumqty FROM dbo.Orders GROUP BY GROUPING SETS((empid, custid), (empid), (custid), ());

最后推荐一个学习T-SQL的网站,http://tsql.solidq.com/,有空可以去看看,有英文原版的学习视频和资料。

参考资料:

  1. (美)本咁. SQL Server 2012 T-SQL基础教程[M]. 北京:人民邮电出版社, 2013.

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Web项目聚集地

Oracle知识点总结(一)

这里用1,也是为了方便,当然如果数据量较大的话,也可以提高速度,因为写count(*)的话会所有列扫描,这里用1的话或者用字段名的话,只扫描你写的那个列其实1就...

911
来自专栏battcn

MySQL - RANGE优化篇

对于单列索引,索引值区间可以方便地用WHERE语句中的相应范围条件表示。优化器在常量传播阶段,会将一些非常量值转换为常量。

2023
来自专栏芋道源码1024

数据库中间件 Sharding-JDBC 源码分析 —— SQL 解析(三)之查询SQL

1. 概述 2. SelectStatement 2.1 AbstractSQLStatement 2.2 SQLToken 3. #query() 3.1 ...

4028
来自专栏后端技术探索

分表查询统计的一个具体案例

问题描述 mysql数据库在数据量较大的情况下,对数据表进行水平分表,按照年份,如下:

841
来自专栏醉程序

学习笔记 | MySQL 数据类型

1413
来自专栏乐沙弥的世界

批量 SQL 之 FORALL 语句

    对PL/SQL而言,任何的PL/SQL块或者子程序都是PL/SQL引擎来处理,而其中包含的SQL语句则由PL/SQL引擎发送SQL语句转交到SQL引擎...

872
来自专栏me的随笔

T-SQL基础(三)之子查询与表表达式

在嵌套查询中,最外面查询结果集返回给调用方,称为外部查询。嵌套在外部查询内的查询称为子查询,子查询的结果集供外部查询使用。

951
来自专栏乐沙弥的世界

SQL基础-->分组与分组函数

使用group by column1,column2,..按columm1,column2进行分组,即column1,column2组合相同的值为一个组

1302
来自专栏AhDung

【SQL】分配函数一枚[AllotToTable]

像这种分配法我估计在多种场景都有在用,它应该有个名堂的,只是我不知道,还请知道的猿友告知,谢谢。

1271
来自专栏me的随笔

T-SQL基础(三)之子查询与表表达式

在嵌套查询中,最外面查询结果集返回给调用方,称为外部查询。嵌套在外部查询内的查询称为子查询,子查询的结果集供外部查询使用。

1174

扫码关注云+社区

领取腾讯云代金券