另一个提高效率的技巧是使用 DISTINCT 关键字查找数据行的单独报表，来代替使用 GROUP BY 子句。在这种情况下，使用 DISTINCT 关键字的 SQL 效率更高。请在需要计算聚合函数（SUM、COUNT、MAX 等）的情况下再使用 GROUP BY。另外，如果您的查询总是自己返回一个唯一的行，则不要使用 DISTINCT 关键字。在这种情况下，DISTINCT 关键字只会增加系统开销。 _com
您已经看到了，有大量技术都可用于优化查询和实现特定的业务规则，技巧就是进行一些尝试，然后比较它们的性能。最重要的是要测试、测试、再测试。 中国站长.站
以下为引用的内容：
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, @com 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 Www~~com @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)) Www^^com 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' _com 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 Www@@com 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 Www..com 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 ^com FROM @tblFinal f ORDER BY f.CompanyName, f.sMonth 中国站.长.站
中.国.站.长.站
文章整理：西部数码--专业提供域名注册、虚拟主机服务
http://www.west263.com
以上信息与文章正文是不可分割的一部分,如果您要转载本文章,请保留以上信息，谢谢!
