四个语句帮你提高 SQL Server 的伸缩性(5)

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