.customerID ;INNER JOIN OrderDetails D ;ON O.orderid = D.orderid ;GROUP BY C.customerID, Cpanyname, O.orderID ;ORDER BY pctTotal DESC 这个SELECT语句返回客户ID、公司名称、销售额以及销售额占总销售额的百分比。注意在以上语句中,子查询充当SELECT
列表中的一个复杂表达式,并且它还包含一个聚集函数SUM(),这里我们可以看到使用它的灵活性。子查询的另一种使用场景即派生表,实际你可以将它看作一个逻辑表。考虑以下的例子:SELECT ;C.customerid, ;P.product_count AS p_count;FROM Customers C, ;(SELECT c2.customerid, ; COUNT(DISTINCT D.productID) AS p_count ;FROM Customers C2 ;INNER JOIN Orders O ;ON C2.customerid = O.customerid ;INNER JOIN OrderDetails D ;ON O.orderid = D.orderid ;GROUP BY c2.customerid) AS P ;WHERE C.customerID = p.customerID ;AND P.p_count >= ;(SELECT (COUNT(*)*.50) FROM Products) ;ORDER BY p.product_count DESC