利用 GROUP BY HAVING 解决复杂的问题

问题一：找到平均交货时间超过所有供应商的平均交货时间的供应商（挑选低效率的供应商）

查找交货时间大于2015 季度平均水平的供应商

SELECT v.VendName,
       AVG(DATEDIFF(DAY, p.OrderDate, p.DeliveryDate)) DeliveryDays
FROM dbo.Vendors v
    JOIN dbo.PurchaseOrders p
        ON v.VendorID = p.VendorID
WHERE p.DeliveryDate IS NOT NULL
      AND p.OrderDate >= '2015-10-01 00:00:00'
      AND p.OrderDate < '2016-01-01 00:00:00'
GROUP BY v.VendName
HAVING AVG(DATEDIFF(DAY, p.OrderDate, p.DeliveryDate)) >
(
    SELECT AVG(DATEDIFF(DAY, OrderDate, DeliveryDate))
    FROM dbo.PurchaseOrders
    WHERE DeliveryDate IS NOT NULL
          AND OrderDate >= '2015-10-01 00:00:00'
          AND OrderDate < '2016-01-01 00:00:00'
);

问题二：列出在一定时间内的总销售额大于某个类别下的所有产品的平均销售额的产品（按类别查找畅销产品）

查找2015年第4季度按类别查找最畅销的产品

SELECT c.CategoryDescription,
       p.ProductName,
       SUM(od.QuotedPrice * od.QuantityOrdered) totalsales
FROM dbo.Products p
    JOIN dbo.Order_Details od
        ON p.ProductNumber = od.ProductNumber
    JOIN dbo.Categories c
        ON p.CategoryID = c.CategoryID
    JOIN dbo.Orders o
        ON o.OrderNumber = od.OrderNumber
WHERE o.OrderDate BETWEEN '2015-10-01' AND '2015-12-31'
GROUP BY p.CategoryID, c.CategoryDescription, p.ProductName
HAVING SUM(od.QuotedPrice * od.QuantityOrdered) >
(
    SELECT AVG(sumcategory)
    FROM
    (
        SELECT p2.CategoryID,
               p2.ProductNumber,
               SUM(od2.QuotedPrice * od2.QuantityOrdered) sumcategory
        FROM dbo.Products p2
            JOIN dbo.Order_Details od2
                ON p2.ProductNumber = od2.ProductNumber
            JOIN dbo.Orders o2
                ON o2.OrderNumber = od2.OrderNumber
        WHERE p2.CategoryID = p.CategoryID  --在外部查询中过滤此类别
              AND o2.OrderDate BETWEEN '2015-10-01' AND '2015-12-31'
        GROUP BY p2.CategoryID, p2.ProductNumber
    ) s
    GROUP BY s.CategoryID
)
ORDER BY c.CategoryDescription, p.ProductName;