拾遗补零
这里介绍其他一些可帮助提高 SQL 查询效率的常用技术。假设您将按区域对所有销售人员进行分组并将他们的销售额进行小计，但是您只想要那些数据库中标记为处于活动状态的销售人员。您可以按区域对销售人员分组，并使用 HAVING 子句消除那些未处于活动状态的销售人员，也可以在 WHERE 子句中执行此操作。在 WHERE 子句中执行此操作会减少需要分组的行数，所以比在 HAVING 子句中执行此操作效率更高。HAVING 子句中基于行的条件的筛选会强制查询对那些在 WHERE 子句中会被去除的数据进行分组。
另一个提高效率的技巧是使用 DISTINCT 关键字查找数据行的单独报表，来代替使用 GROUP BY 子句。在这种情况下，使用 DISTINCT 关键字的 SQL 效率更高。请在需要计算聚合函数(SUM、COUNT、MAX 等)的情况下再使用 GROUP BY。另外，如果您的查询总是自己返回一个唯一的行，则不要使用 DISTINCT 关键字。在这种情况下，DISTINCT 关键字只会增加系统开销。
您已经看到了，有大量技术都可用于优化查询和实现特定的业务规则，技巧就是进行一些尝试，然后比较它们的性能。最重要的是要测试、测试、再测试。
|
Figure 2 Returning All Customers and Their Sales set nocount on DECLARE @dtStartDate DATETIME, @dtEndDate DATETIME, @dtDate DATETIME SET @dtEndDate = '5/5/1997' SET @dtEndDate = DATEADD(DD, -1, CAST(CAST((MONTH(@dtEndDate) + 1) AS VARCHAR(2)) + '/01/' + CAST(YEAR(@dtEndDate) AS VARCHAR(4)) + ' 23:59:59' AS DATETIME)) SET @dtStartDate = DATEADD(MM, -1 * 12, @dtEndDate) SELECT CAST(YEAR(o.OrderDate) AS VARCHAR(4)) + '-' + CASE WHEN MONTH(o.OrderDate) < 10 THEN '0' + CAST(MONTH(o.OrderDate) AS VARCHAR(2)) ELSE CAST(MONTH(o.OrderDate) AS VARCHAR(2)) END AS sMonth, c.CustomerID, c.CompanyName, c.ContactName, SUM(od.Quantity * od.UnitPrice) AS mSales FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID INNER JOIN [Order Details] od ON o.OrderID = od.OrderID WHERE o.OrderDate BETWEEN @dtStartDate AND @dtEndDate GROUP BY CAST(YEAR(o.OrderDate) AS VARCHAR(4)) + '-' + CASE WHEN MONTH(o.OrderDate) < 10 THEN '0' + CAST(MONTH(o.OrderDate) AS VARCHAR(2)) ELSE CAST(MONTH(o.OrderDate) AS VARCHAR(2)) END, c.CustomerID, c.CompanyName, c.ContactName ORDER BY c.CompanyName, sMonth Figure 3 Cartesian Product at Work DECLARE @tblMonths TABLE (sMonth VARCHAR(7)) DECLARE @tblCustomers TABLE ( CustomerID CHAR(10), CompanyName VARCHAR(50), ContactName VARCHAR(50)) DECLARE @tblFinal TABLE ( sMonth VARCHAR(7), CustomerID CHAR(10), CompanyName VARCHAR(50), ContactName VARCHAR(50), mSales MONEY) DECLARE @dtStartDate DATETIME, @dtEndDate DATETIME, @dtDate DATETIME, @i INTEGER SET @dtEndDate = '5/5/1997' SET @dtEndDate = DATEADD(DD, -1, CAST(CAST((MONTH(@dtEndDate) + 1) AS VARCHAR(2)) + '/01/' + CAST(YEAR(@dtEndDate) AS VARCHAR(4)) + ' 23:59:59' AS DATETIME)) SET @dtStartDate = DATEADD(MM, -1 * 12, @dtEndDate) — Get all months into the first table SET @i = 0 WHILE (@i < 12) BEGIN SET @dtDate = DATEADD(mm, -1 * @i, @dtEndDate) INSERT INTO @tblMonths SELECT CAST(YEAR(@dtDate) AS VARCHAR(4)) + '-' + CASE WHEN MONTH(@dtDate) < 10 THEN '0' + CAST(MONTH(@dtDate) AS VARCHAR(2)) ELSE CAST(MONTH(@dtDate) AS VARCHAR(2)) END AS sMonth SET @i = @i + 1 END — Get all clients who had sales during that period into the "y" table INSERT INTO @tblCustomers SELECT DISTINCT c.CustomerID, c.CompanyName, c.ContactName FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID WHERE o.OrderDate BETWEEN @dtStartDate AND @dtEndDate INSERT INTO @tblFinal SELECT m.sMonth, c.CustomerID, c.CompanyName, c.ContactName, 0 FROM @tblMonths m CROSS JOIN @tblCustomers c UPDATE @tblFinal SET mSales = mydata.mSales FROM @tblFinal f INNER JOIN ( SELECT c.CustomerID, CAST(YEAR(o.OrderDate) AS VARCHAR(4)) + '-' + CASE WHEN MONTH(o.OrderDate) < 10 THEN '0' + CAST(MONTH(o.OrderDate) AS VARCHAR(2)) ELSE CAST(MONTH(o.OrderDate) AS VARCHAR(2)) END AS sMonth, SUM(od.Quantity * od.UnitPrice) AS mSales FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID INNER JOIN [Order Details] od ON o.OrderID = od.OrderID WHERE o.OrderDate BETWEEN @dtStartDate AND @dtEndDate GROUP BY c.CustomerID, CAST(YEAR(o.OrderDate) AS VARCHAR(4)) + '-' + CASE WHEN MONTH(o.OrderDate) < 10 THEN '0' + CAST(MONTH(o.OrderDate) AS VARCHAR(2)) ELSE CAST(MONTH(o.OrderDate) AS VARCHAR(2)) END ) mydata on f.CustomerID = mydata.CustomerID AND f.sMonth = mydata.sMonth SELECT f.sMonth, f.CustomerID, f.CompanyName, f.ContactName, f.mSales FROM @tblFinal f ORDER BY f.CompanyName, f.sMonth |
