第二章 单表查询
第二章 单表查询
2.1 SELECT语句的元素
SELECT语句的目的是对表进行查询，应用一定的逻辑处理，并返回结果。
示例1 示例查询
USE TSQLFundamentals2008;
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;
各子句在逻辑上按一下顺序进行处理：
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
逻辑上应该按照一下顺序进行处理：
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid,YEAR(orderdate)
HAVING COUNT(*) > 1
SELECT empid,YEAR(orderdate) AS orderyear , COUNT(*) AS numorders
ORDER BY empid,orderyear
2.1.1 FROM子句
在FROM子句中指定想要查询的表名，在SELECT子句中指定想要返回的各个属性。
SELECT orderid,custid,empid,orderdate,freight FROM Sales.Orders
2.1.2 WHERE子句
只有能让逻辑表达式返回TRUE的行，才能由WHERE阶段返回给后续的逻辑处理阶段。
SELECT orderid,empid,custid,freight FROM Sales.Orders WHERE custid=71
T-SQL使用的是三值谓词逻辑： TRUE FALSE UNKNOWN。
【注意】 TRUE != NOT FALSE
2.1.3 GROUP BY子句
GROUP BY阶段可以将前面逻辑查询处理阶段返回的行按“组”进行组合。每个组由在GROUP BY子句中指定的各元素决定。
如果涉及到分组，那么GROUP BY阶段之后的所有阶段（包含HAVING，SELECT以及ORDER BY）的操作对象都是组，而不是单独的行。
因为聚合函数只为每个组返回一个值，所以一个元素如果不在GROUP BY列表中出现，就只能作为聚合函数（COUNT，MAX，SUM，AVG，MIN）的输入。
所有的聚合函数都会忽略NULL值，只有一个例外——COUNT(*)
SELECT empid,YEAR(orderdate),SUM(freight),COUNT(*) AS orderdate
FROM Sales.Orders
WHERE custid=71
GROUP BY empid,YEAR(orderdate)
YEAR函数orderdate列的年份部分。
SELECT empid,YEAR(orderdate),COUNT(DISTINCT custid)
FROM Sales.Orders
GROUP BY empid,YEAR(orderdate)
如果只想处理不重复的已知值，可以在聚合函数括号中指定DISTINCT 关键字。
2.1.3 HAVING子句
只有能让逻辑表达式返回TRUE的组，才能由HAVING阶段返回给后续的逻辑处理阶段。
SELECT empid,YEAR(orderdate)
FROM Sales.Orders
WHERE custid=71
GROUP BY empid,YEAR(orderdate)
HAVING COUNT(*)>1
2.1.5 SELECT子句
SELECT子句用于指定需要在查询返回的结果集中包含的属性（列）。
为目标列取别名： 表达式 AS 别名（最佳实践）
【注意】SELECT子句是在FROM，WHERE，GROUP BY以及HAVING子句之后处理的，这意味着对于SELECT子句之前处理的那些子句，在SELECT子句中为表达式分配的别名并不存在。
错误示例1：
SELECT orderid,YEAR(orderdate) as orderyear
FROM Sales.Orders
WHERE orderyear>2006
解决办法是使用同一个表达式：
SELECT orderid,YEAR(orderdate) as orderyear
FROM Sales.Orders
WHERE YEAR(orderdate)>2006
在SELECT子句内部也不能引用同一SELECT子句中创建的别名列。
错误示例2：
SELECT YEAR(orderdate) AS orderyear,orderyear+1 AS nextyear
FROM Sales.Orders
为了确保SELECT语句执行的结果中行的唯一性，SQL提供的方法就是使用DISTINCT子句来删除多余的行。
SELECT DISTINCT empid , YEAR(orderdate) AS orderyear
FROM Sales.Orders
2.1.6 ORDER BY子句
ORDER BY子句用于展示数据时对输出结果中的行进行排序（升序ASC和降序DESC）。
ORDER BY子句是唯一能引用SELECT处理阶段创建的列别名的阶段，因为它是唯一一个在SELECT阶段之后被处理的阶段。
SELECT empid,YEAR(orderdate) AS orderyear
FROM Sales.Orders
ORDER BY empid,orderyear ASC
T-SQL支持在ORDER BY子句中指定没有在SELECT子句中出现过的元素。也就是说，排序依据的列并不一定要在输出返回的列中选取。
SELECT firstname,lastname,country
FROM HR.Employees
ORDER BY hiredate DESC;
【注意】
当指定了DISTINCT以后，ORDER BY子句就被限制为只能选取SELECT列表中出现的那些元素。
错误示例3：
SELECT DISTINCT country
FROM HR.Employees
ORDER BY hiredate;
2.1.7 TOP选项
TOP选项是T-SQL特有的，用于限制查询返回的行数或百分比。
从逻辑查询的角度来看，TOP选项是作为SELECT阶段的一部分而处理的，紧挨着DISTINCT子句处理之后（如果存在DISTINCT）。
【注意】
当在查询中指定了TOP之后，ORDER BY子句就会起到双重作用。
（1）作为SELECT处理阶段的一部分的TOP选项要依靠ORDER BY子句先为各个行定义它们的逻辑优先顺序，在这种顺序的基础上再去过滤其他请求。
（2）作为SELECT处理阶段之后的ORDER BY阶段，与为了展示数据而对行进行排序的ORDER BY子句完全一样。
SELECT TOP(5) orderid,orderdate
FROM Sales.Orders
ORDER BY orderdate DESC
在TOP中可以使用PERCENT关键字，在这种情况下，SQL Server会按照百分比来计算应该返回的满足条件的行数（向上取整）。
SELECT TOP(2) PERCENT orderid,orderdate
FROM Sales.Orders
ORDER BY orderdate DESC
如果想让查询是确定的，就要让ORDER BY列表能唯一的决定一行。换句话说，就是要为ORDER BY列表增加一个附加属性（附加属性是一个允许唯一的排列元素的属性或属性列表）。
WITH TIES选项：
SELECT TOP(5) WITH TIES orderid,orderdate
FROM Sales.Orders
ORDER BY orderdate DESC;
2.1.7 OVER子句
OVER子句用于为行定义一个窗口，以便进行特定的运算。可以把行的窗口简单的认为是运算将要操作的一个行的集合。
聚合开窗函数使用OVER子句提供窗口作为上下文，对窗口中的一组值进行操作，而不是使用GROUP BY子句提供的上下文。这样就不必对数据进行分组，还能在同一行中同时返回基础行的列和聚合列。
带有空括号的OVER子句会提供所有的行进行计算。这里的所有的行并不一定是在FROM子句中出现的那些表中的所有行，而是在FROM，WHERE，GROUP BY，HAVING处理阶段完成之后仍然可用的那些行。
只有在SELECT阶段和ORDER BY阶段才允许使用OVER子句。
SELECT orderid,custid,val,SUM(val) OVER() AS total ,SUM(val) OVER(PARTITION BY custid) AS custtotalvalue
FROM Sales.OrderValues
OVER子句的一个优点就是：
（1）能够在返回基本列的同时，在同一个行对它们进行聚合。
（2）也可以在表达式中混合使用基本列和聚合值列。
SELECT orderid,custid,val,100.*val / SUM(val) OVER() , 100.*val/SUM(val)
OVER(PARTITION BY custid)
FROM Sales.OrderValues;
OVER也支持四种排名函数：
ROW_NUMBER：用于为查询的结果集的各行分配递增的序列号，其逻辑顺序通过OVER子句中的ORDER BY语句进行指定。
SELECT orderid,custid,ROW_NUMBER() OVER(ORDER BY orderid) AS rownumber
FROM Sales.OrderValues
RANK和DENSE_RANK：RANK表示之前具有多少行更低的排序值
DENSE_RANK表示之前具有多少个更低的排序值
SELECT orderid,custid,val ,ROW_NUMBER() OVER(ORDER BY val) AS rownumber,
RANK() OVER(ORDER BY custid) AS rank,
DENSE_RANK() OVER(ORDER BY custid) AS denserank
FROM Sales.OrderValues
【注意】
RANK和DENSE_RANK不同于ROW_NUMBER的地方是：
RANK和DENSE_RANK为具有相同排序值的所有行生成同一个序列号！
NTILE：可以把结果集中的行关联到组（tile,相当于由行组成的指定数目的组），并为每一行分配一个所属组的编号。
SELECT orderid,custid,val,NTILE(10) OVER(ORDER BY val)
FROM Sales.OrderValues
