第二章 单表查询(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;

--From子句
select orderid, custid, empid, orderdate, freight from Sales.Orders;

--Where子句 Where阶段只返回让逻辑表达式为True的那些行。要时刻记住T-SQL使用的是三值谓词逻辑，
--所以逻辑表达式的结果可以为TRUE、False，或者UNKNOWN。
select orderid, empid, orderdate, freight from Sales.Orders where custid = 71;

--Group by子句 如果涉及到分组，那么Group by阶段之后的所有阶段的操作对象将是组，而不是单独的
--行。每个组最终也表示为查询结果集中的一行。
--所有的聚合函数都会忽略null值，只有一个例外——Count(*)
--如果只想处理不重复的已知值，可以在聚合函数的圆括号中指定Distinct关键字
select empid, YEAR(orderdate) as orderyear , SUM(freight) as totalfreight, COUNT(distinct custid) as numorders 
from Sales.Orders group by empid, YEAR(orderdate);

--Having子句 只有能让Having子句中的逻辑表达式为True的组,Having阶段才会把这些组返回到下一个逻
--辑查询处理阶段
select empid, YEAR(orderdate) as orderyear, COUNT(*) as numorders
from Sales.Orders where custid=71 group by empid, YEAR(orderdate) having COUNT(*)>1;

--为了却表select语句执行结果中行的唯一性,SQL提供的方法就是使用Distinct子句来删除重复的行
select distinct empid, YEAR(orderdate) as orderyear from Sales.Orders where custid=71

--理解SQL最重要的一点就是要明白表不保证是有序的,因为表是为了代表一个集合,而集合是无序的。
--Order by 是唯一能够引用Select处理阶段创建的别名列的阶段，因为它是唯一一个在Select阶段之后被
--处理的阶段。
--当要按照表达式的升序排序时，可以在表达式的后面指定ASC关键字，因为ASC是默认值。如果想按照降序
--排序，则要在表达式后面指定DESC关键字。
--当指定了Distinct以后,order by子句就被限制为只能选取在Select列表中出现的那些元素
select distinct empid, YEAR(orderdate) as orderyear from Sales.Orders order by empid asc, orderyear asc;

--T-SQL支持在Order By子句中指定没有在Select子句中出现过的元素，也就是说排序依据的列并不一定必
--须要在输出返回的列中选取
select empid, firstname, lastname, country from HR.Employees order by hiredate;

--Top选项是T-SQL特有的,用于限制查询返回的行数或百分比
Select top(5) orderid, orderdate, custid, empid 
from Sales.Orders order by orderdate desc;

--在top选项中可以使用percent关键字，在这种情况下，SQL Server会按照百分比来计算该返回的满足条件的行数
--（向上取整）
select top(1) percent orderid, orderdate, custid, empid
from Sales.Orders order by orderdate desc;

--还能够请求返回与TOP n行中最后一行的排序值相同的其他所有行。为此，须增加一个with ties选项
select top(5) with ties orderid, orderdate, custid, empid
from Sales.Orders order by orderdate desc;

--只有在Select和Order by处理阶段才允许使用Over子句
--如果想对行进行限制或分区,则可以使用partition by子句
select orderid,custid,val, SUM(val) over() as totalvalue, SUM(val) over(partition by custid) as custtotalvalue
from Sales.OrderValues

--Over子句的一个优点就是能够在返回基本列的同时，在同一行对它们进行聚合
select orderid, custid, val, 100.*val/SUM(val) over() as pctall, 100.*val/SUM(val) over(partition by custid) as pctcust 
from Sales.OrderValues

--Row_Number函数用于为查询的结果集中的各行分配递增的序列号,其逻辑顺序通过over子句中的order by语句进行指定
select orderid, custid, val, row_number() over (partition by custid order by val) as rownum from sales.ordervalues order by custid, val