第五章 表表达式(3)

use TSQLFundamentals2008;
GO
--1-1 返回每个雇员处理过的订单的最近日期
select e.empid, o.orderdate as maxorderdate
from HR.Employees as e cross apply
(select top(1) orderdate from Sales.Orders
where e.empid = empid
order by orderdate desc) as o
order by o.orderdate

select empid, MAX(orderdate) as maxorderdate
from Sales.Orders
group by empid

--1-2 用1-1的表联接查询返回每个雇员订单日期最近的所有订单
select e.empid, o.orderdate, o.orderid, o.custid
from Sales.Orders as o, 
(select empid, MAX(orderdate) as maxorderdate
from Sales.Orders
group by empid) as e
where o.orderdate = e.maxorderdate 
and o.empid = e.empid

--2-1 为每个订单按照orderdate,orderid的顺序来计算其行号
select o.orderid, o.orderdate, o.custid, o.empid, row_number() over(order by o.orderid, o.orderdate) as rownum
from Sales.Orders as o 
 
--2-2 用CTE封装2-1 返回行号在11-20之间的行
with sortorders as 
(select o.orderid, o.orderdate, o.custid, o.empid, row_number() over(order by o.orderid, o.orderdate) as rownum
from Sales.Orders o )
select top(10) * from sortorders
where rownum not in (select top(10) rownum from sortorders);

--3 使用递归CTE,返回领导Zoya Dolgopyatova的管理链
with managelink as
(select e1.empid, e1.mgrid, e1.firstname, e1.lastname
from HR.Employees e1 where e1.firstname = N'Zoya' and e1.lastname = N'Dolgopyatova'
union all 
select e2.empid, e2.mgrid, e2.firstname, e2.lastname
from HR.Employees e2, managelink m where e2.empid = m.mgrid)
select * from managelink

--4-1 创建一个视图,返回每个雇员每年处理的总订货量
if OBJECT_ID(N'Sales.VEmpOrders') is not null
drop view Sales.VEmpOrders;
go

create view Sales.VEmpOrders with encryption, schemabinding 
as
(select o.empid, YEAR(o.orderdate) as orderyear, SUM(od.qty) as qty
from Sales.Orders o, Sales.OrderDetails od
where o.orderid = od.orderid
group by o.empid, YEAR(o.orderdate)) with check option;
go

select * from Sales.VEmpOrders order by empid, orderyear;
go

--4-2 返回每个雇员每年处理过的连续总订货量
with VEmp as 
(select v1.empid, v1.orderyear, v1.qty, v1.qty as runqty
from Sales.VEmpOrders v1
where v1.orderyear = (select MIN(v2.orderyear)
from Sales.VEmpOrders v2 where v1.empid = v2.empid)
union all
select v3.empid, v3.orderyear, v3.qty, v3.qty+v4.runqty as runqty
from Sales.VEmpOrders v3, VEmp v4
where v3.empid=v4.empid and v3.orderyear=v4.orderyear+1)
select * from VEmp order by empid, orderyear;

--5-1 创建一个内联函数,其输入参数为供应商ID(@supid as int)和要求的产品数量(@n as int).
--该函数返回给供应商@supid提供的产品中，单价最高的@n个产品
if OBJECT_ID(N'Production.fn_TopProducts') is not null
drop function Production.fn_TopProducts;
go

create function Production.fn_TopProducts(@supid as int, @n as int)
returns table
as 
return 
select top(@n) productid, productname, unitprice
from Production.Products
where supplierid = @supid
order by unitprice desc;
go

select * from Production.fn_TopProducts(5,2);
go

--5-2 使用CROSS APPLY运算符和5-1中创建的函数,为每个供应商返回两个价格最贵的产品
select s.supplierid, s.companyname, f.productid, f.productname, f.unitprice
from Production.Suppliers s
cross apply Production.fn_TopProducts(s.supplierid, 2) as f