第四章 子查询(2)
use TSQLFundamentals2008;--1返回Orders表中活动的最后一天生成的所有订单select orderid, orderdate, custid, empidfrom Sales.Orders as o1where orderdate = (select MAX(o2.orderdate) from Sales.Orders as o2)--2返回拥有订单数量最多的客户下过的所有订单select custid, orderid, orderdate, empidfrom Sales.Orders as o1where 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, lastnamefrom 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 cwhere not exists (select e.country from HR.Employees e where c.country = e.country)select distinct c.country from Sales.Customers cwhere c.country not in (select e.country from HR.Employees e)--5为每个客户返回在他参与活动的最后一天下过的所有订单select custid, orderid, orderdate, empidfrom Sales.Orders o1where o1.orderdate =(select max(orderdate) from Sales.Orders o2where o1.custid = o2.custidgroup by o2.custid)order by custid--6返回在2007年下过订单,而在2008年没有下过订单的客户select custid, companyname from Sales.Customers as cwhere 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.companynamefrom Sales.Customers c, Sales.Orders o, Sales.OrderDetails odwhere 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 co2where co2.ordermonth<=co1.ordermonth and co1.custid = co2.custid)from Sales.CustOrders co1order by custid
