四项技术 助你提高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