学步园

独立子查询

独立标量子查询

相关子查询

not in () 的null值处理.

 

--返回由拥有订单数目最多的客户下过的所有订单. 注意, 多个客户可能下过的订单数目是一样的
--因此用到了with ties子句
--select o.custid, o.orderid, o.orderdate, o.empid from Sales.Orders o where custid in
--(
--    select top 1 with ties custid from Sales.Orders group by custid order by COUNT(*) desc
--)

--返回'2008-05-01'之后就没有处理过订单的员工
--select * from HR.Employees e
--where not exists(select * from Sales.Orders o where orderdate>='2008-05-01' and o.empid=e.empid)

--返回在客户表中出现过, 但是没有在员工表中出现过的国家
--select distinct country from Sales.Customers where country not in 
--(select country from hr.Employees where country is not null)

--为每个客户返回在他参加活动的最后一天下过的所有订单.
--select * from Sales.Orders o inner join 
--(
--    select max(orderdate) dt, custid from Sales.Orders group by custid
--) t on o.custid=t.custid and o.orderdate=t.dt

--返回2007年下过订单, 但是08年没有下过订单的客户
--select * from Sales.Orders o where YEAR(orderdate)=2007
--and not exists(select custid from Sales.Orders o2 where o.custid=o2.custid and YEAR(orderdate)=2008)

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

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