1.《SQLSERVER2012之T-SQL教程》T-SQL单表查询（一）

2019独角兽企业重金招聘Python工程师标准>>>

表结构与数据：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语句的逻辑顺序为：

1. FORM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

    为了更易于阅读，下面的各子句的功能：

1. 从Sales.Orders表查询行；
2. 仅筛选客户ID等于71的订单；
3. 按雇员ID和订单年度对订单进行分组；
4. 筋筛选出大于1个订单的组；
5. 返回每组的雇员ID、订单年度和订单数量；
6. 按雇员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列表不是唯一的，就有可能存在多个正确结果（没有决胜属性）。

转载于:https://my.oschina.net/u/2981366/blog/775133