sql - 为什么用 "extra"列进行数据透视不合并结果

Question

我知道你们中许多人都观察到了这种行为，但是我想知道是否有人可以解释原因。当我创建一个小表以创建使用数据透视函数的示例时，我得到的结果是我期望的:

CREATE TABLE dbo.AverageFishLength
    (
      Fishtype VARCHAR(50) ,
      AvgLength DECIMAL(8, 2) ,
      FishAge_Years INT
    )
INSERT  INTO dbo.AverageFishLength
        ( Fishtype, AvgLength, FishAge_Years )
VALUES  ( 'Muskie', 32.75, 3 ),
        ( 'Muskie', 37.5, 4 ),
        ( 'Muskie', 39.75, 5 ),
        ( 'Walleye', 16.5, 3 ),
        ( 'Walleye', 18.25, 4 ),
        ( 'Walleye', 20.0, 5 ),
        ( 'Northern Pike', 20.75, 3 ),
        ( 'Northern Pike', 23.25, 4 ),
        ( 'Northern Pike', 26.0, 5 );

这是数据透视查询:

SELECT  Fishtype ,
        [3] AS [3 Years Old] ,
        [4] AS [4 Years Old] ,
        [5] AS [5 Years Old]
FROM    dbo.AverageFishLength   PIVOT( SUM(AvgLength) 
                                FOR FishAge_Years IN ( [3], [4], [5] ) ) AS PivotTbl

结果如下:

但是，如果我创建带有标识列的表，则结果将分成几行:

DROP TABLE dbo.AverageFishLength
CREATE TABLE dbo.AverageFishLength
    (
      ID INT IDENTITY(1,1) ,
      Fishtype VARCHAR(50) ,
      AvgLength DECIMAL(8, 2) ,
      FishAge_Years INT
    )
INSERT  INTO dbo.AverageFishLength
        ( Fishtype, AvgLength, FishAge_Years )
VALUES  ( 'Muskie', 32.75, 3 ),
        ( 'Muskie', 37.5, 4 ),
        ( 'Muskie', 39.75, 5 ),
        ( 'Walleye', 16.5, 3 ),
        ( 'Walleye', 18.25, 4 ),
        ( 'Walleye', 20.0, 5 ),
        ( 'Northern Pike', 20.75, 3 ),
        ( 'Northern Pike', 23.25, 4 ),
        ( 'Northern Pike', 26.0, 5 );

完全相同的查询:

SELECT  Fishtype ,
        [3] AS [3 Years Old] ,
        [4] AS [4 Years Old] ,
        [5] AS [5 Years Old]
FROM    dbo.AverageFishLength   PIVOT( SUM(AvgLength) 
                                FOR FishAge_Years IN ( [3], [4], [5] ) ) AS PivotTbl

不同的结果:

在我看来，ID列正在查询中使用，即使它根本没有出现在查询中。它几乎就像隐式包含在查询中一样，但未显示在结果集中。

谁能解释为什么会这样？

Answer 1

发生这种情况是因为ID列对于每一行都是唯一的，并且由于您直接查询该表(没有子查询)，所以该列作为GROUP BY的一部分包含在聚合函数中。
MSDN docs about FROM 的文档说明以下内容:

table_source PIVOT <pivot_clause>

Specifies that the table_source is pivoted based on the pivot_column. table_source is a table or table expression. The output is a table that contains all columns of the table_source except the pivot_column and value_column. The columns of the table_source, except the pivot_column and value_column, are called the grouping columns of the pivot operator.

PIVOT performs a grouping operation on the input table with regard to the grouping columns and returns one row for each group. Additionally, the output contains one column for each value specified in the column_list that appears in the pivot_column of the input_table.

您的版本基本上是在说 SELECT * FROM yourtable和PIVOT这样的数据。即使 ID列不在最终的SELECT列表中，它也是查询中的分组元素。如果将PIVOT与“pre-PIVOT”示例进行比较以显示，则将看到您的版本。本示例使用CASE表达式和聚合函数:

SELECT Fishtype,
  sum(case when FishAge_Years = 3 then AvgLength else 0 end) as [3],
  sum(case when FishAge_Years = 4 then AvgLength else 0 end) as [4],
  sum(case when FishAge_Years = 5 then AvgLength else 0 end) as [5]
FROM dbo.AverageFishLength
GROUP BY Fishtype, ID;

结果将是歪斜的，因为即使最终列表中没有 ID，它仍被用于分组，并且由于它们是唯一的，因此您会得到多行。
使用PIVOT时解决此问题的最简单方法是使用子查询:

SELECT Fishtype ,
        [3] AS [3 Years Old] ,
        [4] AS [4 Years Old] ,
        [5] AS [5 Years Old]
FROM
(
  SELECT Fishtype, 
    AvgLength, 
    FishAge_Years
  FROM    dbo.AverageFishLength
) d
PIVOT
( 
  SUM(AvgLength) 
  FOR FishAge_Years IN ( [3], [4], [5] ) 
) AS PivotTbl;

在此版本中，您仅返回表中实际需要和想要的列-这不包括 ID，因此不会用于对数据进行分组。