sql server 2008语言基础: 表表达式

--返回每个雇员处理过的订单的最近日期.
--select empid, max(orderdate) as maxOrderDate from Sales.Orders group by empid
--封装上面的结果集, 并和order表连接, 为每个雇员返回其订单日期最近的订单信息.
/*
with t as (
select empid, max(orderdate) as maxOrderDate from Sales.Orders group by empid
)
select t.empid, t.maxOrderDate, o.orderid, o.custid 
from t inner join Sales.Orders o on t.empid=o.empid and o.orderdate=t.maxOrderDate
*/
--为每个订单按照orderdate, orderid的顺序来计算其行号.
--select orderid, orderdate, custid, empid, ROW_NUMBER() over(order by orderdate, orderid) rownum 
--from Sales.Orders
/*
with T as (
    select orderid, orderdate, custid, empid, ROW_NUMBER() over(order by orderdate, orderid) rownum 
from Sales.Orders
)
select * from T where rownum>10 and rownum<=20
*/

--使用cte递归来设计一个解决方案, 返回领导id为9 的管理链.
/*
with t as (
    select empid,mgrid,firstname,lastname from hr.Employees where empid=9
    union all
    select e.empid,e.mgrid,e.firstname,e.lastname from hr.Employees e inner join t on e.empid=t.mgrid
)
select * from t
*/

--返回每个雇员每年处理的总订货量
/*
create VIEW [dbo].[V_1]
AS
SELECT     TOP (100) PERCENT Sales.Orders.empid, YEAR(Sales.Orders.orderdate) AS orderYear, SUM(Sales.OrderDetails.qty) AS totalQty
FROM         Sales.OrderDetails INNER JOIN
                      Sales.Orders ON Sales.OrderDetails.orderid = Sales.Orders.orderid
GROUP BY Sales.Orders.empid, YEAR(Sales.Orders.orderdate)
ORDER BY Sales.Orders.empid, orderYear
*/
--使用上面这个视图, 返回每个雇员每年处理过的连续总订货量
select *,(select SUM(totalQty) from V_1 v1 where v1.empid=v2.empid and v1.orderyear<=v2.orderyear) from dbo.V_1 v2
order by empid,orderyear