主页 / dba / 问题 / 163448
Accepted
Kaishu
Asked: 2017-02-08 03:13:56 +0800 CST

如何获取同一张表上不同列的计数

  • 772

表#01 Status：

StatusID    Status
-----------------------
 1          Opened
 2          Closed
 3          ReOpened
 4          Pending

表#02 Claims：

ClaimID     CompanyName StatusID
--------------------------------------
1               ABC     1
2               ABC     1
3               ABC     2
4               ABC     4
5               XYZ     1
6               XYZ     1

预期结果：

CompanyName TotalOpenClaims TotalClosedClaims TotalReOpenedClaims TotalPendingClaims
--------------------------------------------------------------------------------
ABC                 2           1                      0               1
XYZ                 2           0                      0               0

我需要如何编写查询才能获得预期的结果？

sql-server pivot

3 个回答

  1. Best Answer

    SUM()使用andCASE语句最简单：

    select CompanyName, 
sum(case when StatusID=1 then 1 else 0 end) as TotalOpenClaims,
sum(case when StatusID=2 then 1 else 0 end) as TotalClosedClaims,
sum(case when StatusID=3 then 1 else 0 end) as TotalReOpenedClaims,
sum(case when StatusID=4 then 1 else 0 end) as TotalPendingClaims
from Claims
group by CompanyName;
    • 29

  2. 这是一个典型的枢轴转换，Phil 建议的条件聚合是实现它的好方法。

    还有一种更现代的语法可以实现相同的结果，它使用 PIVOT 子句：

    SELECT
  CompanyName,
  TotalOpenClaims     = [1],
  TotalClosedClaims   = [2],
  TotalReOpenedClaims = [3],
  TotalPendingClaims  = [4]
FROM
  dbo.Claims
  PIVOT
  (
    COUNT(ClaimID)
    FOR StatusID IN ([1], [2], [3], [4])
  ) AS p
;

    在内部，这种看起来更简单的语法等价于 Phil 的 GROUP BY 查询。更准确地说，它等价于这种变化：

    SELECT
  CompanyName,
  TotalOpenClaims     = COUNT(CASE WHEN StatusID = 1 THEN ClaimID END),
  TotalClosedClaims   = COUNT(CASE WHEN StatusID = 2 THEN ClaimID END),
  TotalReOpenedClaims = COUNT(CASE WHEN StatusID = 3 THEN ClaimID END),
  TotalPendingClaims  = COUNT(CASE WHEN StatusID = 4 THEN ClaimID END)
FROM
  dbo.Claims
GROUP BY
  CompanyName
;

    因此，PIVOT 查询本质上是隐式 GROUP BY 查询。

    然而，众所周知，PIVOT 查询在处理方面比具有条件聚合的显式 GROUP BY 查询更棘手。当您使用 PIVOT 时，您需要始终牢记这一点：

    • ClaimsPIVOT 子句中未明确提及的数据集的所有列（在这种情况下）都是 GROUP BY 列。

    如果Claims仅包含示例中显示的三列，则上面的 PIVOT 查询将按预期工作，因为显然CompanyName是 PIVOT 中未明确提及的唯一列，因此最终成为隐式 GROUP BY 的唯一标准。

    但是，如果Claims有其他列（例如ClaimDate），它们将被隐式用作附加的 GROUP BY 列——也就是说，您的查询本质上是在做

    GROUP BY CompanyName, ClaimDate, ... /* whatever other columns there are*/`

    结果很可能不是您想要的。

    不过，这很容易解决。为了从参与隐式分组中排除不相关的列，您可以只使用派生表，您将只选择结果所需的列，尽管这会使查询看起来不那么优雅：

    SELECT
  CompanyName,
  TotalOpenClaims     = [1],
  TotalClosedClaims   = [2],
  TotalReOpenedClaims = [3],
  TotalPendingClaims  = [4]
FROM
  (SELECT ClaimID, CompanyName, StatusID FROM dbo.Claims) AS derived
  PIVOT
  (
    COUNT(ClaimID)
    FOR StatusID IN ([1], [2], [3], [4])
  ) AS p
;

    尽管如此，如果Claims已经是派生表，则无需添加另一层嵌套，只需确保在当前派生表中仅选择生成输出所需的列。

    您可以在手册中阅读有关 PIVOT 的更多信息：

    • 18

  3. 诚然，我的经验主要是使用 MySQL，而且我没有在 SQL Server 上花费太多时间。如果以下查询不起作用，我会感到非常惊讶：

    SELECT 
  CompanyName, 
  status, 
  COUNT(status) AS 'Total Claims' 
FROM Claim AS c 
  JOIN Status AS s ON c.statusId = s.statusId 
GROUP BY 
  CompanyName, 
  status;

    这不会以您想要的格式为您提供输出，但它确实为您提供了您想要的所有信息，尽管忽略了零案例。这对我来说感觉比在查询中处理 CASE 语句要简单得多，如果它只是用于格式化，那感觉就像一个特别糟糕的主意。

    • 1

相关问题