sql – 为什么使用“额外”列进行透视不会合并结果

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 ),39.75,5 ),( 'Walleye',16.5,18.25,20.0,( 'Northern Pike',20.75,23.25,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),5 );

同样的查询：

SELECT  Fishtype,[5] ) ) AS PivotTbl

结果不同：

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

谁能解释为什么会这样？

解决方法

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,[5] AS [5 Years Old]
FROM
(
  SELECT Fishtype,FishAge_Years
  FROM    dbo.AverageFishLength
) d
PIVOT
( 
  SUM(AvgLength) 
  FOR FishAge_Years IN ( [3],[5] ) 
) AS PivotTbl;

在此版本中,您只返回表中实际需要和想要的列 – 这会排除ID,因此不会用于对数据进行分组.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!