有時(shí)，為了讓?xiě)?yīng)用程序運(yùn)行得更快，所做的全部工作就是在這里或那里做一些很小調(diào)整。啊，但關(guān)鍵在于確定如何進(jìn)行調(diào)整！遲早您會(huì)遇到這種情況：應(yīng)用程序中的 SQL 查詢(xún)不能按照您想要的方式進(jìn)行響應(yīng)。它要么不返回?cái)?shù)據(jù)，要么耗費(fèi)的時(shí)間長(zhǎng)得出奇。如果它降低了報(bào)告或您的企業(yè)應(yīng)用程序的速度，用戶(hù)必須等待的時(shí)間過(guò)長(zhǎng)，他們就會(huì)很不滿(mǎn)意。就像您的父母不想聽(tīng)您解釋為什么在深更半夜才回來(lái)一樣，用戶(hù)也不會(huì)聽(tīng)你解釋為什么查詢(xún) 耗費(fèi)這么長(zhǎng)時(shí)間。（“對(duì)不起，媽媽?zhuān)沂褂昧颂嗟?LEFT JOIN。”）用戶(hù)希望應(yīng)用程序響應(yīng)迅速，他們的報(bào)告能夠在瞬間之內(nèi)返回分析數(shù)據(jù)。就我自己而言，如果在 Web 上沖浪時(shí)某個(gè)頁(yè)面要耗費(fèi)十多秒才能加載（好吧，五秒更實(shí)際一些），我也會(huì)很不耐煩。
為了解決這些問(wèn)題，重要的是找到問(wèn)題的根源。那么，從哪里開(kāi)始呢？根本原因通常在于
數(shù)據(jù)庫(kù)設(shè)計(jì)和訪(fǎng)問(wèn)它的查詢(xún)。在本月的專(zhuān)欄中，我將講述四項(xiàng)技術(shù)，這些技術(shù)可用于提高基于
SQL Server? 的應(yīng)用程序的性能或改善其可伸縮性。我將仔細(xì)說(shuō)明 LEFT JOIN、CROSS JOIN 的使用以及 IDENTITY 值的檢索。請(qǐng)記住，根本沒(méi)有神奇的解決方案。調(diào)整您的
數(shù)據(jù)庫(kù)及其查詢(xún)需要占用時(shí)間、進(jìn)行分析，還需要大量的測(cè)試。這些技術(shù)都已被證明行之有效，但對(duì)您的應(yīng)用程序而言，可能其中一些技術(shù)比另一些技術(shù)更適用。
從 INSERT 返回 IDENTITY
我決定從遇到許多問(wèn)題的內(nèi)容入手：如何在執(zhí)行 SQL INSERT 后檢索 IDENTITY 值。通常，問(wèn)題不在于如何編寫(xiě)檢索值的查詢(xún)，而在于在哪里以及何時(shí)進(jìn)行檢索。在
SQL Server 中，下面的語(yǔ)句可用于檢索由最新在活動(dòng)
數(shù)據(jù)庫(kù)連接上運(yùn)行的 SQL 語(yǔ)句所創(chuàng)建的 IDENTITY 值：
SELECT @@IDENTITY
這個(gè) SQL 語(yǔ)句并不復(fù)雜，但需要記住的一點(diǎn)是：如果這個(gè)最新的 SQL 語(yǔ)句不是 INSERT，或者您針對(duì)非 INSERT SQL 的其他連接運(yùn)行了此 SQL，則不會(huì)獲得期望的值。您必須運(yùn)行下列代碼才能檢索緊跟在 INSERT SQL 之后且位于同一連接上的 IDENTITY，如下所示：
INSERT INTO Products (ProductName) VALUES ('Chalk')
SELECT @@IDENTITY
在一個(gè)連接上針對(duì) Northwind
數(shù)據(jù)庫(kù)運(yùn)行這些查詢(xún)將返回一個(gè)名稱(chēng)為 Chalk 的新產(chǎn)品的 IDENTITY 值。所以，在使用 ADO 的 Visual Basic? 應(yīng)用程序中，可以運(yùn)行以下語(yǔ)句：
Set oRs = oCn.Execute("SET NOCOUNT ON;INSERT INTO Products _
(ProductName) VALUES ('Chalk');SELECT @@IDENTITY")
lProductID = oRs(0)
此代碼告訴
SQL Server 不要返回查詢(xún)的行計(jì)數(shù)，然后執(zhí)行 INSERT 語(yǔ)句，并返回剛剛為這個(gè)新行創(chuàng)建的 IDENTITY 值。SET NOCOUNT ON 語(yǔ)句表示返回的記錄集有一行和一列，其中包含了這個(gè)新的 IDENTITY 值。如果沒(méi)有此語(yǔ)句，則會(huì)首先返回一個(gè)空的記錄集（因?yàn)?INSERT 語(yǔ)句不返回任何數(shù)據(jù)），然后會(huì)返回第二個(gè)記錄集，第二個(gè)記錄集中包含 IDENTITY 值。這可能有些令人困惑，尤其是因?yàn)槟鷱膩?lái)就沒(méi)有希望過(guò) INSERT 會(huì)返回記錄集。之所以會(huì)發(fā)生此情況，是因?yàn)?
SQL Server 看到了這個(gè)行計(jì)數(shù)（即一行受到影響）并將其解釋為表示一個(gè)記錄集。因此，真正的數(shù)據(jù)被推回到了第二個(gè)記錄集。當(dāng)然您可以使用 ADO 中的 NextRecordset 方法獲取此第二個(gè)記錄集，但如果總能夠首先返回該記錄集且只返回該記錄集，則會(huì)更方便，也更有效率。
此方法雖然有效，但需要在 SQL 語(yǔ)句中額外添加一些代碼。獲得相同結(jié)果的另一方法是在 INSERT 之前使用 SET NOCOUNT ON 語(yǔ)句，并將 SELECT @@IDENTITY 語(yǔ)句放在表中的 FOR INSERT 觸發(fā)器中，如下面的代碼片段所示。這樣，任何進(jìn)入該表的 INSERT 語(yǔ)句都將自動(dòng)返回 IDENTITY 值。
CREATE TRIGGER trProducts_Insert ON Products FOR INSERT AS
SELECT @@IDENTITY
GO
觸發(fā)器只在 Products 表上發(fā)生 INSERT 時(shí)啟動(dòng)，所以它總是會(huì)在成功 INSERT 之后返回一個(gè) IDENTITY。使用此技術(shù)，您可以始終以相同的方式在應(yīng)用程序中檢索 IDENTITY 值。
內(nèi)嵌視圖與臨時(shí)表
某些時(shí)候，查詢(xún)需要將數(shù)據(jù)與其他一些可能只能通過(guò)執(zhí)行 GROUP BY 然后執(zhí)行標(biāo)準(zhǔn)查詢(xún)才能收集的數(shù)據(jù)進(jìn)行聯(lián)接。例如，如果要查詢(xún)最新五個(gè)定單的有關(guān)信息，您首先需要知道是哪些定單。這可以使用返回定單 ID 的 SQL 查詢(xún)來(lái)檢索。此數(shù)據(jù)就會(huì)存儲(chǔ)在臨時(shí)表（這是一個(gè)常用技術(shù)）中，然后與 Products 表進(jìn)行聯(lián)接，以返回這些定單售出的產(chǎn)品數(shù)量：
CREATE TABLE #Temp1 (OrderID INT NOT NULL, _
OrderDate DATETIME NOT NULL)
INSERT INTO #Temp1 (OrderID, OrderDate)
SELECT TOP 5 o.OrderID, o.OrderDate
FROM Orders o ORDER BY o.OrderDate DESC
SELECT p.ProductName, SUM(od.Quantity) AS ProductQuantity
FROM #Temp1 t
INNER JOIN [Order Details] od ON t.OrderID = od.OrderID
INNER JOIN Products p ON od.ProductID = p.ProductID
GROUP BY p.ProductName
ORDER BY p.ProductName
DROP TABLE #Temp1
這些 SQL 語(yǔ)句會(huì)創(chuàng)建一個(gè)臨時(shí)表，將數(shù)據(jù)插入該表中，將其他數(shù)據(jù)與該表進(jìn)行聯(lián)接，然后除去該臨時(shí)表。這會(huì)導(dǎo)致此查詢(xún)進(jìn)行大量 I/O 操作，因此，可以重新編寫(xiě)查詢(xún)，使用內(nèi)嵌視圖取代臨時(shí)表。內(nèi)嵌視圖只是一個(gè)可以聯(lián)接到 FROM 子句中的查詢(xún)。所以，您不用在 tempdb 中的臨時(shí)表上耗費(fèi)大量 I/O 和磁盤(pán)訪(fǎng)問(wèn)，而可以使用內(nèi)嵌視圖得到同樣的結(jié)果：
SELECT p.ProductName,
SUM(od.Quantity) AS ProductQuantity
FROM (
SELECT TOP 5 o.OrderID, o.OrderDate
FROM Orders o
ORDER BY o.OrderDate DESC
) t
INNER JOIN [Order Details] od ON t.OrderID = od.OrderID
INNER JOIN Products p ON od.ProductID = p.ProductID
GROUP BY
p.ProductName
ORDER BY
p.ProductName
此查詢(xún)不僅比前面的查詢(xún)效率更高，而且長(zhǎng)度更短。臨時(shí)表會(huì)消耗大量資源。如果只需要將數(shù)據(jù)聯(lián)接到其他查詢(xún)，則可以試試使用內(nèi)嵌視圖，以節(jié)省資源。
避免 LEFT JOIN 和 NULL
當(dāng)然，有很多時(shí)候您需要執(zhí)行 LEFT JOIN 和使用 NULL 值。但是，它們并不適用于所有情況。改變 SQL 查詢(xún)的構(gòu)建方式可能會(huì)產(chǎn)生將一個(gè)花幾分鐘運(yùn)行的報(bào)告縮短到只花幾秒鐘這樣的天壤之別的效果。有時(shí)，必須在查詢(xún)中調(diào)整數(shù)據(jù)的形態(tài)，使之適應(yīng)應(yīng)用程序所要求的顯示方式。雖然 TABLE 數(shù)據(jù)類(lèi)型會(huì)減少大量占用資源的情況，但在查詢(xún)中還有許多區(qū)域可以進(jìn)行優(yōu)化。SQL 的一個(gè)有價(jià)值的常用功能是 LEFT JOIN。它可以用于檢索第一個(gè)表中的所有行、第二個(gè)表中所有匹配的行、以及第二個(gè)表中與第一個(gè)表不匹配的所有行。例如，如果希望返回每個(gè)客戶(hù)及其定單，使用 LEFT JOIN 則可以顯示有定單和沒(méi)有定單的客戶(hù)。
此工具可能會(huì)被過(guò)度使用。LEFT JOIN 消耗的資源非常之多，因?yàn)樗鼈儼c NULL（不存在）數(shù)據(jù)匹配的數(shù)據(jù)。在某些情況下，這是不可避免的，但是代價(jià)可能非常高。LEFT JOIN 比 INNER JOIN 消耗資源更多，所以如果您可以重新編寫(xiě)查詢(xún)以使得該查詢(xún)不使用任何 LEFT JOIN，則會(huì)得到非常可觀的回報(bào)。
加快使用 LEFT JOIN 的查詢(xún)速度的一項(xiàng)技術(shù)涉及創(chuàng)建一個(gè) TABLE 數(shù)據(jù)類(lèi)型，插入第一個(gè)表（LEFT JOIN 左側(cè)的表）中的所有行，然后使用第二個(gè)表中的值更新 TABLE 數(shù)據(jù)類(lèi)型。此技術(shù)是一個(gè)兩步的過(guò)程，但與標(biāo)準(zhǔn)的 LEFT JOIN 相比，可以節(jié)省大量時(shí)間。一個(gè)很好的規(guī)則是嘗試各種不同的技術(shù)并記錄每種技術(shù)所需的時(shí)間，直到獲得用于您的應(yīng)用程序的執(zhí)行性能最佳的查詢(xún)。
測(cè)試查詢(xún)的速度時(shí)，有必要多次運(yùn)行此查詢(xún)，然后取一個(gè)平均值。因?yàn)椴樵?xún)（或存儲(chǔ)過(guò)程）可能會(huì)存儲(chǔ)在
SQL Server 內(nèi)存中的過(guò)程緩存中，因此第一次嘗試耗費(fèi)的時(shí)間好像稍長(zhǎng)一些，而所有后續(xù)嘗試耗費(fèi)的時(shí)間都較短。另外，運(yùn)行您的查詢(xún)時(shí)，可能正在針對(duì)相同的表運(yùn)行其他查詢(xún)。當(dāng)其他查詢(xún)鎖定和解鎖這些表時(shí)，可能會(huì)導(dǎo)致您的查詢(xún)要排隊(duì)等待。例如，如果您進(jìn)行查詢(xún)時(shí)某人正在更新 此表中的數(shù)據(jù)，則在更新提交時(shí)您的查詢(xún)可能需要耗費(fèi)更長(zhǎng)時(shí)間來(lái)執(zhí)行。
避免使用 LEFT JOIN 時(shí)速度降低的最簡(jiǎn)單方法是盡可能多地圍繞它們?cè)O(shè)計(jì)
數(shù)據(jù)庫(kù)。例如，假設(shè)某一產(chǎn)品可能具有類(lèi)別也可能沒(méi)有類(lèi)別。如果 Products 表存儲(chǔ)了其類(lèi)別的 ID，而沒(méi)有用于某個(gè)特定產(chǎn)品的類(lèi)別，則您可以在字段中存儲(chǔ) NULL 值。然后您必須執(zhí)行 LEFT JOIN 來(lái)獲取所有產(chǎn)品及其類(lèi)別。您可以創(chuàng)建一個(gè)值為“No Category”的類(lèi)別，從而指定外鍵關(guān)系不允許 NULL 值。通過(guò)執(zhí)行上述操作，現(xiàn)在您就可以使用 INNER JOIN 檢索所有產(chǎn)品及其類(lèi)別了。雖然這看起來(lái)好像是一個(gè)帶有多余數(shù)據(jù)的變通方法，但可能是一個(gè)很有價(jià)值的技術(shù)，因?yàn)樗梢韵?SQL 批處理語(yǔ)句中消耗資源較多的 LEFT JOIN。在
數(shù)據(jù)庫(kù)中全部使用此概念可以為您節(jié)省大量的處理時(shí)間。請(qǐng)記住，對(duì)于您的用戶(hù)而言，即使幾秒鐘的時(shí)間也非常重要，因?yàn)楫?dāng)您有許多用戶(hù)正在訪(fǎng)問(wèn)同一個(gè)聯(lián)機(jī)
數(shù)據(jù)庫(kù)應(yīng)用程序時(shí)，這幾秒鐘實(shí)際上的意義會(huì)非常重大。
靈活使用笛卡爾乘積
對(duì)于此技巧，我將進(jìn)行非常詳細(xì)的介紹，并提倡在某些情況下使用笛卡爾乘積。出于某些原因，笛卡爾乘積 (CROSS JOIN) 遭到了很多譴責(zé)，開(kāi)發(fā)人員通常會(huì)被警告根本就不要使用它們。在許多情況下，它們消耗的資源太多，從而無(wú)法高效使用。但是像 SQL 中的任何工具一樣，如果正確使用，它們也會(huì)很有價(jià)值。例如，如果您想運(yùn)行一個(gè)返回每月數(shù)據(jù)（即使某一特定月份客戶(hù)沒(méi)有定單也要返回）的查詢(xún)，您就可以很方便地使用笛卡爾乘積。
雖然這看起來(lái)好像沒(méi)什么神奇的，但是請(qǐng)考慮一下，如果您從客戶(hù)到定單（這些定單按月份進(jìn)行分組并對(duì)銷(xiāo)售額進(jìn)行小計(jì)）進(jìn)行了標(biāo)準(zhǔn)的 INNER JOIN，則只會(huì)獲得客戶(hù)有定單的月份。因此，對(duì)于客戶(hù)未訂購(gòu)任何產(chǎn)品的月份，您不會(huì)獲得 0 值。如果您想為每個(gè)客戶(hù)都繪制一個(gè)圖，以顯示每個(gè)月和該月銷(xiāo)售額，則可能希望此圖包括月銷(xiāo)售額為 0 的月份，以便直觀標(biāo)識(shí)出這些月份。如果使用 Figure 2（最后一頁(yè)） 中的 SQL，數(shù)據(jù)則會(huì)跳過(guò)銷(xiāo)售額為 0 美元的月份，因?yàn)樵诙▎伪碇袑?duì)于零銷(xiāo)售額不會(huì)包含任何行（假設(shè)您只存儲(chǔ)發(fā)生的事件）。
Figure 3（最后一頁(yè)）中的代碼雖然較長(zhǎng)，但是可以達(dá)到獲取所有銷(xiāo)售數(shù)據(jù)（甚至包括沒(méi)有銷(xiāo)售額的月份）的目標(biāo)。首先，它會(huì)提取去年所有月份的列表，然后將它們放入第一個(gè) TABLE 數(shù)據(jù)類(lèi)型表 (@tblMonths) 中。下一步，此代碼會(huì)獲取在該時(shí)間段內(nèi)有銷(xiāo)售額的所有客戶(hù)公司的名稱(chēng)列表，然后將它們放入另一個(gè) TABLE 數(shù)據(jù)類(lèi)型表 (@tblCus-tomers) 中。這兩個(gè)表存儲(chǔ)了創(chuàng)建結(jié)果集所必需的所有基本數(shù)據(jù)，但實(shí)際銷(xiāo)售數(shù)量除外。 第一個(gè)表中列出了所有月份（12 行），第二個(gè)表中列出了這個(gè)時(shí)間段內(nèi)有銷(xiāo)售額的所有客戶(hù)（對(duì)于我是 81 個(gè)）。并非每個(gè)客戶(hù)在過(guò)去 12 個(gè)月中的每個(gè)月都購(gòu)買(mǎi)了產(chǎn)品，所以，執(zhí)行 INNER JOIN 或 LEFT JOIN 不會(huì)返回每個(gè)月的每個(gè)客戶(hù)。這些操作只會(huì)返回購(gòu)買(mǎi)產(chǎn)品的客戶(hù)和月份。
笛卡爾乘積則可以返回所有月份的所有客戶(hù)。笛卡爾乘積基本上是將第一個(gè)表與第二個(gè)表相乘，生成一個(gè)行集合，其中包含第一個(gè)表中的行數(shù)與第二個(gè)表中的行數(shù)相乘的結(jié)果。因此，笛卡爾乘積會(huì)向表 @tblFinal 返回 972 行。最后的步驟是使用此日期范圍內(nèi)每個(gè)客戶(hù)的月銷(xiāo)售額總計(jì)更新 @tblFinal 表，以及選擇最終的行集。
如果由于笛卡爾乘積占用的資源可能會(huì)很多，而不需要真正的笛卡爾乘積，則可以謹(jǐn)慎地使用 CROSS JOIN。例如，如果對(duì)產(chǎn)品和類(lèi)別執(zhí)行了 CROSS JOIN，然后使用 WHERE 子句、DISTINCT 或 GROUP BY 來(lái)篩選出大多數(shù)行，那么使用 INNER JOIN 會(huì)獲得同樣的結(jié)果，而且效率高得多。如果需要為所有的可能性都返回?cái)?shù)據(jù)（例如在您希望使用每月銷(xiāo)售日期填充一個(gè)圖表時(shí)），則笛卡爾乘積可能會(huì)非常有幫助。但是，您不應(yīng)該將它們用于其他用途，因?yàn)樵诖蠖鄶?shù)方案中 INNER JOIN 的效率要高得多。
拾遺補(bǔ)零
這里介紹其他一些可幫助提高 SQL 查詢(xún)效率的常用技術(shù)。假設(shè)您將按區(qū)域?qū)λ袖N(xiāo)售人員進(jìn)行分組并將他們的銷(xiāo)售額進(jìn)行小計(jì)，但是您只想要那些
數(shù)據(jù)庫(kù)中標(biāo)記為處于活動(dòng)狀態(tài)的銷(xiāo)售人員。您可以按區(qū)域?qū)︿N(xiāo)售人員分組，并使用 HAVING 子句消除那些未處于活動(dòng)狀態(tài)的銷(xiāo)售人員，也可以在 WHERE 子句中執(zhí)行此操作。在 WHERE 子句中執(zhí)行此操作會(huì)減少需要分組的行數(shù)，所以比在 HAVING 子句中執(zhí)行此操作效率更高。HAVING 子句中基于行的條件的篩選會(huì)強(qiáng)制查詢(xún)對(duì)那些在 WHERE 子句中會(huì)被去除的數(shù)據(jù)進(jìn)行分組。
另一個(gè)提高效率的技巧是使用 DISTINCT 關(guān)鍵字查找數(shù)據(jù)行的單獨(dú)報(bào)表，來(lái)代替使用 GROUP BY 子句。在這種情況下，使用 DISTINCT 關(guān)鍵字的 SQL 效率更高。請(qǐng)?jiān)谛枰?jì)算聚合函數(shù)（SUM、COUNT、MAX 等）的情況下再使用 GROUP BY。另外，如果您的查詢(xún)總是自己返回一個(gè)唯一的行，則不要使用 DISTINCT 關(guān)鍵字。在這種情況下，DISTINCT 關(guān)鍵字只會(huì)增加系統(tǒng)開(kāi)銷(xiāo)。
您已經(jīng)看到了，有大量技術(shù)都可用于優(yōu)化查詢(xún)和實(shí)現(xiàn)特定的業(yè)務(wù)規(guī)則，技巧就是進(jìn)行一些嘗試，然后比較它們的性能。最重要的是要測(cè)試、測(cè)試、再測(cè)試。在此專(zhuān)欄的將來(lái)各期內(nèi)容中，我將繼續(xù)深入講述
SQL Server 概念，包括
數(shù)據(jù)庫(kù)設(shè)計(jì)、好的索引實(shí)踐以及
SQL Server 安全范例。
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
來(lái)源：http://www.chinahtml.com