表结构与数据:https://github.com/XuePeng87/TSQLV4
SELECT语句的元素
SELECT语句的用途的查询表,通过一些逻辑操作来返回一个结果。例如:
SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)
HAVING COUNT(*) > 1
ORDER BY empid, orderyear;
此查询的意义是:
筛选客户71名下的订单,按雇员和订单年度进行分组,并且仅帅选大于1个订单的“雇员和订单”组。对于保留的分组,此查询提供了雇员ID、订单年度和订单数量,并按照雇员ID和订单年度进行排序。
其SQL语句的逻辑顺序为:
- FORM
- WHERE
- GROUP BY
- HAVING
- SELECT
- ORDER BY
为了更易于阅读,下面的各子句的功能:
- 从Sales.Orders表查询行;
- 仅筛选客户ID等于71的订单;
- 按雇员ID和订单年度对订单进行分组;
- 筋筛选出大于1个订单的组;
- 返回每组的雇员ID、订单年度和订单数量;
- 按雇员ID和订单排序输出行;
FORM子句
FROM子句是罗计划处理的第一个查询子句,此子句制定要查询的表名。
FROM Sales.Orders;
要在代码中始终使用架构限定式的对象名称。如果不显示指定架构名称,SQL Server必须基于其隐式名称解析规则来确定所属架构,这会造成一些不必要的额外支出,并且会导致SQL Server选择不同的对象,而不是所期望的对象。
WHERE子句
在WHERE子句中,可以定义一个谓词或逻辑表达式来筛选由FROM阶段返回的行。只有逻辑表达式计算结果为TRUE的行,才会返回到后面的逻辑查询处理阶段。
SELECT * FROM Sales.Orders WHERE custid = 71;
表Sales.Orders中有830行数据,经过WHERE阶段筛选客户ID等于71后,只有31行。
在谈到查询性能时,WHERE子句具有重要的意义。基于筛选表达式,SQL Server将评估访问请求数据要使用的索引。通过使用索引,相比全表扫描,SQL Server有时可以用更少的工作获得所需的数据。相比返回所有可能行给调用者并在客户端进行筛选的方式(返回表的所有数据,由程序代码进行过滤),筛选也可以减少网络通信量。
WHERE阶段仅返回逻辑表达式计算结果为TRUE的行。T-SQL使用三值谓词逻辑,计算结果可以为:TRUE、FALSE或UNKNOWN。也就是说,WHERE阶段不会返回计算结果为FALSE或UNKONOWN的行。
GROUP BY子句
GROUP BY阶段允许用户把前面逻辑查询处理阶段返回的行排列到组中。组是根据你在GROUP BY子句中制定的元素而确定的。例如:
SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate);
这意味着,GROUP BY阶段对于WHERE阶段返回的数据,会为雇员ID与订单年度值的每个唯一组合生成一个组。表达式YEAR(orderdate)调用YEAR函数仅返回orderdate列的“年”部分。
WHERE阶段返回了31行,其中有16个雇员ID与年度订单值的唯一组合,这样GROUP BY阶段创建16个组,并将WHERE阶段返回的31行,每行都关联到相关组。
如果查询使用了分组,那么后续阶段的HAVING、SELECT和ORDER BY都是对组进行操作,而不是对但各行进行操作了。
对于每个GOURP BY的元素,在每个组中智能有一个唯一匹配项。例如,在雇员ID8和订单2007年的组中,只有一个唯一的雇员ID值和一个唯一的订单年度值。因此,你可以在GROUP BY之后的子句中(如SELECT)引用表达式empid和YEAR(orderdate),如上面例子中的写法。
不参与到GROUP BY列表中的元素仅允许作为一个组合函数的输入,如COUNT、SUM、AVG、MIN或MAX。例如:
SELECT empid, YEAR(orderdate) AS orderyear, SUM(freight) AS totalfreight, COUNT(*) AS numorders
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate);
表达式SUM(greight)返回每组中所有运费的和,函数COUNT(*)返回每组中的订单数量。在GROUP BY子句之后处理的任何子句中,如果试图引用一个未参与到GROUP BY列表中的属性,并且该属性不是作为聚合函数的输入,都会引发错误。
注意,除了COUNT(*)之外,所有聚合函数忽略NULL标记。例如,一个具有5行的组,数值为“30、10、NULL、10、10”在qty列中。表达式COUNT(*)将返回5,而COUNT(qty)将返回4。如果想仅处理已知值中的非重复值,那么可以在聚合函数括号内制定DISTINCT关键字。例如COUNT(DISTINCT qty)将返回2。DISTINCT也可以和其他的聚合函数一起使用,如SUM、AVG等等。
HAVING子句
在HAVING子句中,可以指定一个谓词来筛选组,而不是筛选单个行,行是由WHERE筛选的。只有HAVING子句中逻辑表达式计算结果为TRUE的组,会返回到下一个逻辑查询处理阶段,FALSE或UNKONWN的组会被过滤掉。
由于HAVING子句是在行分组后被处理,所以可以在逻辑表达式中引用聚合函数。
SELECT empid, YEAR(orderdate) AS orderyear, SUM(freight) AS totalfreight, COUNT(*) AS numorders
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)
HAVING COUNT(*) > 1;
例子中的COUNT(*) > 1便是在HAVING子句中使用了聚合函数。执行这段SQL语句后,GOURP BY阶段剩余的16个分组会被过滤到9个分组。
SELECT子句
SELECT子句是用户指定要返回到查询结果表中的列的地方。用户可以基于锁查询的表的列,在SELECT列表中建立表达式,属性可以有进一步的操作(可以使用函数或定义别名),也可以没有。调用某些函数后需要定义别名,如果没有定义别名,那么在查询结果中目标属性将没有名称(没有列名),但是在关系模型中不允许。强烈建议调用某些函数后加入别名,如例子中的YEAR(orderdate) AS orderyear一样。
注意,SELECT是在FROM、WHERE、GROUP BY和HAVING之后处理的。这意味着分配的别名不能在之前的子句中使用。例子中的orderyear是YEAR(orderdate)的别名,如果在WHERE中使用orderyear > 2016会报错。需要使用YEAR(orderdate) > 2016,无需担心YEAR(orderdate)会执行两次,对于查询中重复使用相同的表达式,此表达式继续被计算一次。
关于SELECT *的写法,大多数情况下,不建议使用,只有在极少的情况下例外。在使用星号时,为了解决列的名称可能需要一些额外的工作,虽然这是微不足道的额外开销。
SELECT中不允许引用在一SELECT子句中创建的列别名,不管指定别名的表达式出现试图引用它的表达式的左边还是右边。
ORDER BY子句
SELECT empid, YEAR(orderdate) AS orderyear, SUM(freight) AS totalfreight, COUNT(*) AS numorders
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)
HAVING COUNT(*) > 1
ORDER BY empid, orderyear;
由于ORDER BY子句是在SELECT之后,所以可以使用别名。当希望按照升序(从小到大)排列时,可以在表达式后面加入ASC关键字,如果什么都不加,默认也是ASE排序。如果需要倒序(从大到小)排列时,可以在表达式后面加入DESC关键字。
允许在ORDER BY中使用未出现在SELECT子句中的列,例如:
SELECT empid, firstname, lastname, country
FROM HR.Employees
ORDER BY hiredate;
TOP和OFFSET-FETCH筛选
1) TOP筛选
TOP选项是一个专有的T-SQL功能,用户限定查询返回的行数或行的百分比。他依赖于两个元素作为规范的一部分,一个是要返回行的数目或百分比,另一个是排序。例如,要从Orders表返回最近的5个订单:
SELECT TOP(5) orderid, orderdate FROM Sales.Orders ORDER BY orderdate DESC;
ORDER BY子句是在SELECT所包含的DISTINCT选项之后计算的。这同样适用于TOP,它要依靠ORDER BY为其提供相关的筛选内容,也就是说,TOP筛选是在删除重复行后计算的。
可以为TOP选项指定PERCENT关键字,这种情况下,会基于限定行数的百分比计算要返回的行数,向上舍入。例如:查询最近1%的订单:
SELECT TOP(1) PERCENT orderid, orderdate FROM Sales.Orders ORDER BY orderdate DESC;
查询结果有9行。Orders表中有830行,百分之一为8.3,向上舍入后为9。
如果SELECT中没有定义主键或唯一约束,ORDER BY列表不是唯一的,在这种没有加入决定属性的情况下,会出现重复的行。在这种情况下SQL Server确定行的顺序是基于哪个行首先被物理访问到。如果希望查询结果是确定的,则需要确保ORDER BY列出的数据是唯一的。
出了在ORDER BY列表中添加决胜属性,也可以在SELECT列表中添加决胜属性,可以添加WITH TIES选项实现此目的,例如:
SELECT TOP(5) WITH TIES orderid, orderdate, custid, empid
FROM Sales.Orders ORDER BY orderdate DESC;
结果会返回8行。SQL Server会首先返回基于orderdate DESC排序的TOP(5)行,然后是返回与检索到的5行中最后一行orderdate值相同的其他所有行。
2) OFFSET-FETCH筛选
TOP选项是一个非常实用的筛选类型,但是他有两个缺陷,不是标准SQL,切不支持跳过功能。标准SQL定义的TOP类似的筛选称为OFFSET-FETCH,支持跳过功能,这对针对制定页面的查询非常有用。SQL Server2012引入了对OFFSET-FETCH筛选的支持。
OFFSET-FETCH筛选被视为ORDER BY子句的一部分,通常用于实现按顺序显示效果。OFFSET子句指定要跳过的行数,FETCH子句指定在跳过的行数后要筛选的行数,例如:
SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate, orderid
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;
次查询按照orderdate、orderid排序,并添加了决胜属性排序Orders表中的行。基于此顺序,跳过结果的前50行,筛选下面的25行。
使用OFFSET-FETCH的查询必须具有ORDER BY子句,此外,FETCH子句不支持没有OFFSET。如果不想跳过任何行,但希望使用FETCH,则应当使用OFFSET 0 ROWS来表示。不过,没有FETCH的OFFSET是允许的,这种情况是跳过指定的行数,并返回查询结果中所有的剩余行。
开窗函数速览
开窗函数的功能是:对于基本查询中的每一行,按行的窗口(组)进行运算,并计算一个标量(单个)结果值。行的窗口使用OVER子句定义。例如:
SELECT orderid, custid, val,
ROW_NUMBER() OVER(PARTITION BY custid ORDER BY val) AS rownum
FROM Sales.OrderValues ORDER BY custid, val;
ROW_NUMBER函数对于查询结果各个分区内的行,按照指定的排序,分配了唯一、连续、递增的整数。示例中的OVER子句按照custid属性划分窗口,因此每个客户的行号是唯一的。同时,OVER定义了在窗口中按val属性排序,这样连续行号会在分区内按照val递增。
注意,ROW_NUMBER函数必须在每个分区内生成唯一值。这意味着即使排序值不增加,行号仍旧会递增。因此,如果ROW_NUMBER函数的ORDER BY列表不是唯一的,就有可能存在多个正确结果(没有决胜属性)。