1.每个客户返回一行订单 日期在~到~之间

SELECT E.empid, DATEADD(day, D.n - 1, ‘20090612‘) AS dtFROM HR.Employees AS E CROSS JOIN Nums AS DWHERE D.n <= DATEDIFF(day, ‘20090612‘, ‘20090616‘) + 1ORDER BY empid, dt;

2.返回美国客户 并返回订单数量和总数量

SELECT C.custid, COUNT(DISTINCT O.orderid) AS numorders, SUM(OD.qty) AS totalqtyFROM Sales.Customers AS C JOIN Sales.Orders AS O ON O.custid = C.custid JOIN Sales.OrderDetails AS OD ON OD.orderid = O.orderidWHERE C.country = N‘USA‘GROUP BY C.custid;

3.返回 某个时间段 下单的用户以及订单 还要同时返回 在该时间段 没有下单的用户

SELECT C.custid, C.companyname, O.orderid, O.orderdateFROM Sales.Customers AS C LEFT OUTER JOIN Sales.Orders AS O ON O.custid = C.custid AND O.orderdate = ‘20070212‘;

4.返回某个时间段用户是否有订单

SELECT DISTINCT C.custid, C.companyname, CASE WHEN O.orderid IS NOT NULL THEN ‘Yes‘ ELSE ‘No‘ END AS [HasOrderOn20070212]FROM Sales.Customers AS C LEFT OUTER JOIN Sales.Orders AS O ON O.custid = C.custid AND O.orderdate = ‘20070212‘;