4. Dynamic SQL

使用dynamic SQL可以在以下情況里對(duì)所有的表創(chuàng)建一個(gè)通用查詢：在primary key里沒有包含到列，但是有可兼容的數(shù)據(jù)類型存在時(shí)。

DECLARE@table_name SYSNAME
SELECT@table_name ='dbo.Players'
 
DECLARE@SQL NVARCHAR(MAX)
SELECT@SQL ='
SELECT *
FROM '+ @table_name +'
UNPIVOT (
    value FOR code IN (
        '+ STUFF((
    SELECT', ['+ c.name+']'
    FROMsys.columns cWITH(NOLOCK)
    LEFTJOIN(
        SELECTi.[object_id], i.column_id
        FROMsys.index_columns iWITH(NOLOCK)
        WHEREi.index_id = 1
    ) iONc.[object_id] = i.[object_id]ANDc.column_id = i.column_id
    WHEREc.[object_id] = OBJECT_ID(@table_name)
        ANDi.[object_id]ISNULL
    FORXML PATH(''), TYPE).value('.','NVARCHAR(MAX)'), 1, 2,'') +'
    )
) unpiv'
 
PRINT @SQL
EXECsys.sp_executesql @SQL

其結(jié)果為：

SELECT*
FROM<table_name>
UNPIVOT (
    valueFORcodeIN(<unpivot_column>)
) unpiv

這個(gè)方法在速度上會(huì)比較慢一些，是因?yàn)閁NPIVOT的自動(dòng)生成查詢需要從系統(tǒng)里進(jìn)行額外的讀取以及通過XML trick進(jìn)行“行”的串聯(lián)。

5. XML

一個(gè)更聰明的辦法來執(zhí)行 dynamic UNPIVOT，是通過對(duì)XML做一個(gè)小竅門：

SELECT
      p.PlayerID
    , GameCount = t.c.value('.','INT')
    , GameType = t.c.value('local-name(.)','VARCHAR(10)')
FROM(
    SELECT
          PlayerID
        , [XML] = (
                SELECTWin, Defeat, StandOff
                FORXML RAW('f'), TYPE
            )
    FROMdbo.Players
) p
CROSSAPPLY p.[XML].nodes('f/@*') t(c)

按照以上的查詢， SQL Server會(huì)對(duì)每一個(gè)行都生成一個(gè)XML：

<f Column1="Value1" Column2="Value2" Column3="Value3" ... />

接下來就是屬性的名稱和值被解析。大多數(shù)情況下，XML的使用會(huì)導(dǎo)致一個(gè)更慢的執(zhí)行計(jì)劃：

我們來對(duì)比一下通過執(zhí)行 Compare Selected Results指令的結(jié)果：

我們可以看到，在執(zhí)行查詢的速度上，UNPIVOT和VALUES沒有太明顯的區(qū)別。當(dāng)然，這只是針對(duì)簡(jiǎn)單的將列轉(zhuǎn)化為行的任務(wù)而言。

現(xiàn)在我們用UNPIVOT語句再來做另外一個(gè)實(shí)驗(yàn)：

SELECT
      PlayerID
    , GameType = (
        SELECTTOP1 GameType
        FROMdbo.Players
        UNPIVOT (
            GameCountFORGameTypeIN(
                Win, Defeat, StandOff
            )
        ) unpvt
        WHEREPlayerID = p.PlayerID
        ORDERBYGameCountDESC
    )
FROMdbo.Players p

這次的執(zhí)行計(jì)劃的瓶頸是對(duì)多重?cái)?shù)據(jù)的讀取和排序：

要解決這個(gè)瓶頸其實(shí)是相當(dāng)?shù)暮?jiǎn)單，我們可以使用來自外部查詢的列的模塊就能避免多重?cái)?shù)據(jù)讀取的問題：

SELECT
      p.PlayerID
    , GameType = (
        SELECTTOP1 GameType
        FROM(SELECTt = 1) t
        UNPIVOT (
            GameCountFORGameTypeIN(
                Win, Defeat, StandOff
            )
        ) unpvt
        ORDERBYGameCountDESC
    )
FROMdbo.Players p

這樣一來讀取多重的數(shù)據(jù)就被避免了，但是另外一個(gè)最消耗資源的操作——排序，仍然存在：

接下來就需要VALUES語句來發(fā)揮其作用了：

SELECT
      t.PlayerID
    , GameType = (
            SELECTTOP1 GameType
            FROM(
                VALUES
                      (Win, 'Win')
                    , (Defeat,  'Defeat')
                    , (StandOff,'StandOff')
            ) t (GameCount, GameType)
            ORDERBYGameCountDESC
        )
FROMdbo.Players t

現(xiàn)在就如我們所預(yù)期的那樣，執(zhí)行計(jì)劃被簡(jiǎn)化了，但是排序依然存在：

讓我們嘗試使用aggregation功能來消除掉排序：

SELECT
      t.PlayerID
    , GameType = (
            SELECTTOP1 GameType
            FROM(
                VALUES
                      (Win, 'Win')
                    , (Defeat,  'Defeat')
                    , (StandOff,'StandOff')
            ) t (GameCount, GameType)
            WHEREGameCount = (
                SELECTMAX(Value)
                FROM(
                    VALUES(Win), (Defeat), (StandOff)
                ) t(Value)
            )
        )
FROMdbo.Players t

現(xiàn)在，執(zhí)行計(jì)劃就如下圖所示了：

結(jié)論：當(dāng)我們需要在SQL SERVER里執(zhí)行一個(gè)簡(jiǎn)單的將列轉(zhuǎn)化為行的任務(wù)時(shí)，比較好的選擇是使用 UNPIVOT或者VALUES結(jié)構(gòu)。如果轉(zhuǎn)換后的數(shù)據(jù)行是用作聚合或排序時(shí)，則最好使用VALUES結(jié)構(gòu)，因?yàn)樗苌梢粋€(gè)更有效率的執(zhí)行計(jì)劃。

>>點(diǎn)此免費(fèi)下載試用dbForge Studio for SQL Server