第四章 子查询(2)

use TSQLFundamentals2008;

--1返回Orders表中活动的最后一天生成的所有订单
select orderid, orderdate, custid, empid
from Sales.Orders as o1
where orderdate = 
(select MAX(o2.orderdate) from Sales.Orders as o2)

--2返回拥有订单数量最多的客户下过的所有订单
select custid, orderid, orderdate, empid
from Sales.Orders as o1
where custid=
(select top 1 o2.custid from Sales.Orders as o2 group by o2.custid order by COUNT(*) desc)

--3返回2008年5月1号(包括这一天)以后没有处理过订单的雇员
select empid, Firstname, lastname
from HR.Employees as e 
where e.empid not in
(select o.empid from Sales.Orders as o where o.orderdate>=N'20080501')

--4返回在客户表中出现过,但是在雇员表中没出现过的国家
select distinct c.country from Sales.Customers c
where not exists (select e.country from HR.Employees e where c.country = e.country)

select distinct c.country from Sales.Customers c
where c.country not in (select e.country from HR.Employees e)

--5为每个客户返回在他参与活动的最后一天下过的所有订单
select custid, orderid, orderdate, empid
from Sales.Orders o1
where o1.orderdate =
(select max(orderdate) from Sales.Orders o2
where o1.custid = o2.custid
group by o2.custid)
order by custid

--6返回在2007年下过订单,而在2008年没有下过订单的客户
select custid, companyname 
from Sales.Customers as c
where 
exists(select * from Sales.Orders o where c.custid=o.custid and year(o.orderdate)=2007)
and 
not exists(select * from Sales.Orders o where c.custid=o.custid and YEAR(o.orderdate)=2008)

--7返回订购了第12号产品的客户
select distinct c.custid, c.companyname
from Sales.Customers c, Sales.Orders o, Sales.OrderDetails od
where c.custid=o.custid and o.orderid =od.orderid and od.productid = 12

--8计算每个客户在每个月的连续总订货量
select custid, ordermonth, qty,
(select SUM(co2.qty) from Sales.CustOrders co2
where co2.ordermonth<=co1.ordermonth and co1.custid = co2.custid)
from Sales.CustOrders co1
order by custid