主页 / dba / 问题 / 106128
Accepted
Jmaurier
Asked: 2015-07-07 14:44:41 +0800 CST

枢轴给出太多结果

  • 772

我正在尝试了解这个枢轴的工作原理。我不认为我理解得很正确。我只想对一堆具有某些状态的项目类型进行简单计数。我正在使用的表格包含以下列：[Status]、[ItemType] 和许多其他列。

这是我目前正在做的

SELECT [NEW]
    ,[IN]
    ,[OUT]
    ,READY = [READY FOR DISPOSAL] 
    ,TRANSIT = [IN ROUTE TO DISPOSAL]
    ,[DISPOSED]
    ,[TYPE] = [ItemType] 
FROM [EVAULTTEST].[dbo].[Items] AS ei

PIVOT(
COUNT([Status])
FOR [Status] IN ([NEW], [IN], [DISPOSED]
    , [IN ROUTE TO DISPOSAL], [OUT], [READY FOR DISPOSAL])
) AS piv

结果

1   0   0   0   0   0   FIREARMS
0   1   0   0   0   0   FIREARMS
1   0   0   0   0   0   FIREARMS
0   1   0   0   0   0   FIREARMS
1   0   0   0   0   0   MONEY
1   0   0   0   0   0   Automobiles
0   1   0   0   0   0   Aircraft
1   0   0   0   0   0   Automobiles
1   0   0   0   0   0   Automobiles
1   0   0   0   0   0   ALCOHOL
0   0   1   0   0   0   MONEY
0   0   1   0   0   0   AUTOMOBILES
0   0   1   0   0   0   AUTOMOBILES

为什么这些不折叠成这样的东西：

2   0   0   0   0   0   FIREARMS
0   2   0   0   0   0   FIREARMS
1   0   0   0   0   0   MONEY
3   0   0   0   0   0   Automobiles
0   1   0   0   0   0   Aircraft
1   0   0   0   0   0   ALCOHOL
0   0   1   0   0   0   MONEY
0   0   2   0   0   0   AUTOMOBILES
sql-server sql-server-2008

1 个回答

  1. Best Answer

    它对你的其他专栏感到不安。所以尝试：

    SELECT [NEW]
,[IN]
,[OUT]
,READY = [READY FOR DISPOSAL] 
,TRANSIT = [IN ROUTE TO DISPOSAL]
,[DISPOSED]
,[TYPE] = [ItemType] 
FROM (SELECT ItemType, Status FROM [EVAULTTEST].[dbo].[Items]) AS ei

PIVOT(
COUNT([Status])
FOR [Status] IN ([NEW], [IN], [DISPOSED]
, [IN ROUTE TO DISPOSAL], [OUT], [READY FOR DISPOSAL])
) AS piv

    ...这是因为隐式分组位于不在 PIVOT 子句中的任何列上。因此，删除查询第一部分中的额外列应该可以修复它。

    • 3

相关问题