08 msbi學(xué)習(xí)資料sql高級語法 天善智能

1、天善智能請輸入文字S Q L 高級語法2012-5-22目錄CTE定義及應(yīng)用窗口函數(shù)，分區(qū)函數(shù)應(yīng)用For XML Path應(yīng)用Pivot(行轉(zhuǎn)列)應(yīng)用Merge應(yīng)用動(dòng)態(tài)SQL執(zhí)行結(jié)果賦值給變量ON 后面加And,WHERE區(qū)別，及Except語法天善智能CTE公用表表達(dá)式(CTE) 具有一個(gè)重要的優(yōu)點(diǎn)，那就是能夠引用其自身，從而創(chuàng)建遞歸CTE。遞歸CTE 是一個(gè)重復(fù)執(zhí)行初始CTE 以返回?cái)?shù)據(jù)子集直到獲取完整結(jié)果集的公用表表達(dá)式。在SQL Server 2005 中，當(dāng)某個(gè)查詢引用遞歸CTE 時(shí)，它即被稱為“遞歸查詢”。遞歸查詢通常用于返回分層數(shù)據(jù)，例如：

2、顯示某個(gè)組織圖中的雇員或物料方案（其中父級產(chǎn)品有一個(gè)或多個(gè)組件，而那些組件可能還有子組件，或者是其他父級產(chǎn)品的組件）中的數(shù)據(jù)。天善智能CTE作用遞歸CTE 可以極大地簡化在SELECT、CREATE VIEW INSERT、UPDATE、DELETE 或語句中運(yùn)行遞歸查詢所需的代碼。在SQL Server 的早期版本中，遞歸查詢通常需要使用臨時(shí)表、游標(biāo)和邏輯來控制遞歸步驟流。with cte as(select ROW_NUMBER()over(partition by Num,Name order by Getdate() as RN from A)delete from cte where

3、 RN1天善智能CTE實(shí)現(xiàn)遞歸天善智能窗口函數(shù)窗口函數(shù)和聚集函數(shù)一樣都是對定義的行集（組）進(jìn)行聚集，但是不像聚集一樣只返回 一個(gè)值，窗口函數(shù)可以為每個(gè)組返回多個(gè)值， 執(zhí)行聚集的行組是窗口(因此稱為窗口函數(shù))。窗口函數(shù)是在聚集函數(shù)的基礎(chǔ)上加了一個(gè)over()，所有的聚集函數(shù)都可以利用這種方式轉(zhuǎn)換成窗口函數(shù)。窗口函數(shù)是最后才執(zhí)行的，在order by 之前,where和group by之后天善智能分區(qū)函數(shù)Partition By分區(qū)子句：可以根據(jù)partition by子句定義行的分區(qū)或組，以完成聚集，如果使用空括號，那么整個(gè)結(jié)果集就是分區(qū)，窗口函數(shù)將對它進(jìn)行聚集計(jì)算， 可以把Partition

4、By看成是移動(dòng)的Group By， 可以用Partition By對定義的行組計(jì)算聚集(當(dāng)遇到新的組時(shí)復(fù)位)，并返回每個(gè)值(每個(gè)組中的成員)，而不是用一個(gè)組表示表中這個(gè)值的所有實(shí)例。天善智能窗口函數(shù)與分區(qū)函數(shù)案例天善智能ForXMLPATH定義FOR XML PATH 語句能夠把查詢的數(shù)據(jù)生成XML數(shù)據(jù)在PATH 模式中，列名或列別名被作為XPath表達(dá)式來處理。這些表達(dá)式指明了如何將值映射到XML。每個(gè)XPath表達(dá)式都是一個(gè)相對XPath，它提供了項(xiàng)類型（例如屬性、元素和標(biāo)量值）以及將相對于行元素而生成的節(jié)點(diǎn)的名稱和層次結(jié)構(gòu)。天善智能ForXMLPATH應(yīng)用范圍沒有名稱的列具有名稱的列名稱

5、被指定為通配符(*) 的列以某個(gè)XPath節(jié)點(diǎn)測試的名稱命名的列以指定為data() 的路徑命名的列默認(rèn)情況下包含NULL 值的列天善智能ForXMLPATH應(yīng)用案例天善智能ForXMLPATH應(yīng)用案例天善智能Pivot可以使用PIVOT 和UNPIVOT 關(guān)系運(yùn)算符將表值表達(dá)式更改為另一個(gè)表。PIVOT 通過將表達(dá)式某一列中的唯一值轉(zhuǎn)換為輸出中的多個(gè)列來旋轉(zhuǎn)表值表達(dá)式，并在必要時(shí)對最終輸出中所需的任何其余列值執(zhí)行聚合。UNPIVOT 與PIVOT 執(zhí)行相反的操作，將表值表達(dá)式的列轉(zhuǎn)換為列值。PIVOT 提供的語法比一系列復(fù)雜的SELECT.CASE 語句中所指定的語法更簡單和更具可讀性天善智

6、能Pivot應(yīng)用案例天善智能Merge語法Merge關(guān)鍵字是一個(gè)神奇的DML關(guān)鍵字。它在SQL 2008被引入，它能將INSERT,Update,Delete簡 單的并為一句。MSDN對于Merge的解釋非常的短小精悍:”根據(jù)與源表聯(lián)接的結(jié)果，對目標(biāo)表執(zhí)行插入、更新或刪除操作。例如，根據(jù)在另一 個(gè)表中找到的差異在一個(gè)表中插入、更新或刪 除行，可以對兩個(gè)表進(jìn)行同步?！?通過這個(gè)描述，我們可以看出Merge是關(guān)于對于兩個(gè)表之間的數(shù)據(jù)進(jìn)行操作的。數(shù)據(jù)同步數(shù)據(jù)轉(zhuǎn)換基于源表對目標(biāo)表做Insert,Update,Delete操作天善智能Merge應(yīng)用案例天善智能Merge應(yīng)用案例OUTPUT輸出天善智能M

7、erge應(yīng)用案例條件添加天善智能動(dòng)態(tài)SQLExec sql不符合語法規(guī)則，建議不使用Exec (sql) 符合語法規(guī)則，建議經(jīng)常使用.EXEC SP_EXECUTESQL SQL,-相當(dāng)于存儲(chǔ)過程的函數(shù)體PARAME_VALUE,-相當(dāng)于存儲(chǔ)過程中的參數(shù)定義RETURNVALUE=RETURN_VALUE OUTPUT相當(dāng)于在外面調(diào)用存儲(chǔ)過程MSSQL為我們提供了兩種動(dòng)態(tài)執(zhí)行SQL語句的命令，分別 是EXEC和sp_executesql;通常,sp_executesql則更具有 優(yōu)勢，它提供了輸入輸出接口，而EXEC沒有。還有一個(gè) 最大的好處就是利用sp_executesql，能夠重用執(zhí)行計(jì)劃

8、， 這就大大提供了執(zhí)行性能(對于這個(gè)我在后面的例子中會(huì) 詳加說明)，還可以編寫更安全的代碼。EXEC在某些情況 下會(huì)更靈活。除非您有令人信服的理由使用EXEC，否側(cè) 盡量使用sp_executesql. 天善智能動(dòng)態(tài)SQL執(zhí)行結(jié)果賦值給變量天善智能ON條件數(shù)據(jù)庫在通過連接兩張或多張表來返回記錄時(shí)，都會(huì)生成一張中間的臨時(shí)表，然后再將這張臨時(shí)表返回給用戶。在使用left jion時(shí)，on和where條件的區(qū)別如下：on條件是在生成臨時(shí)表時(shí)使用的條件，它不管on 中的條件是否為真，都會(huì)返回左邊表中的記錄。where條件是在臨時(shí)表生成好后，再對臨時(shí)表進(jìn)行過濾的條件。這時(shí)已經(jīng)沒有l(wèi)eft join的含義（必須返回左邊表的記錄）了，條件不為真的就全部過濾掉。天善智能ON條件應(yīng)用案例天善智能Except語法比較兩個(gè)查詢的結(jié)果，返回非重復(fù)值。EXCEPT