半岛权威十大直营(官方)网站

4. Dynamic SQL

使用dynamic SQL可以在以下情况里对所有的表创建一个通用查询：在primary key里没有包含到列，但是有可兼容的数据类型存在时。

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

其结果为：

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

这个方法在速度上会比较慢一些，是因为UNPIVOT的自动生成查询需要从系统里进行额外的读取以及通过XML trick进行“行”的串联。

5. XML

一个更聪明的办法来执行 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" ... />

接下来就是属性的名称和值被解析。大多数情况下，XML的使用会导致一个更慢的执行计划：

我们来对比一下通过执行 Compare Selected Results指令的结果：

我们可以看到，在执行查询的速度上，UNPIVOT和VALUES没有太明显的区别。当然，这只是针对简单的将列转化为行的任务而言。

现在我们用UNPIVOT语句再来做另外一个实验：

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

这次的执行计划的瓶颈是对多重数据的读取和排序：

要解决这个瓶颈其实是相当的简单，我们可以使用来自外部查询的列的模块就能避免多重数据读取的问题：

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

这样一来读取多重的数据就被避免了，但是另外一个最消耗资源的操作——排序，仍然存在：

接下来就需要VALUES语句来发挥其作用了：

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

现在就如我们所预期的那样，执行计划被简化了，但是排序依然存在：

让我们尝试使用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

现在，执行计划就如下图所示了：

结论：当我们需要在SQL SERVER里执行一个简单的将列转化为行的任务时，比较好的选择是使用 UNPIVOT或者VALUES结构。如果转换后的数据行是用作聚合或排序时，则最好使用VALUES结构，因为它能生成一个更有效率的执行计划。

>>点此免费下载试用dbForge Studio for SQL Server