1.每个客户返回一行订单 日期在~到~之间
SELECT E.empid, , ') AS dt FROM HR.Employees AS E CROSS JOIN Nums AS D ORDER BY empid, dt;
2.返回美国客户 并返回订单数量和总数量
SELECT C.custid, COUNT(DISTINCT O.orderid) AS numorders, SUM(OD.qty) AS totalqty
FROM Sales.Customers AS C
JOIN Sales.Orders AS O
ON O.custid = C.custid
JOIN Sales.OrderDetails AS OD
ON OD.orderid = O.orderid
WHERE C.country = N'USA'
GROUP BY C.custid;
3.返回 某个时间段 下单的用户以及订单 还要同时返回 在该时间段 没有下单的用户
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
LEFT OUTER JOIN Sales.Orders AS O
ON O.custid = C.custid
';
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
';