4. Dynamic SQL

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

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

這個方法在速度上會比較慢一些，是因為UNPIVOT的自動生成查詢需要從系統(tǒng)里進行額外的讀取以及通過XML trick進行“行”的串聯(lián)。

5. XML

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

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會對每一個行都生成一個XML：

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

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

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

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

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

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

這次的執(zhí)行計劃的瓶頸是對多重數(shù)據(jù)的讀取和排序：

要解決這個瓶頸其實是相當(dāng)?shù)暮唵危覀兛梢允褂脕碜酝獠坎樵兊牧械哪K就能避免多重數(shù)據(jù)讀取的問題：

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

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

接下來就需要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í)行計劃被簡化了，但是排序依然存在：

讓我們嘗試使用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í)行計劃就如下圖所示了：

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

>>點此免費下載試用dbForge Studio for SQL Server