sql-server - 计算一列中的所有行，并在结果上获得 2 个不同的计数列，即使没有要计数的记录

Question

我想要做的是对 State 列中的所有行进行计数，并在结果上获得 2 个不同的计数列(每个可能值 1 或 0)。

问题是，即使没有要计数的记录，我也需要在结果上显示 0。

例如，我有这个表子类别，其中包含以下示例数据:

  SubcategoryID |   Name    |
  ---------------------------
        201     |   Name1   |
        202     |   Name2   |
        203     |   Name3   |
        204     |   Name4   |

我有这个表product，其中包含以下示例数据:

  ProductID |Subcategory| State |
  -------------------------------
    101     |   201     |   1   |
    102     |   201     |   1   |
    103     |   201     |   1   |
    104     |   202     |   0   |
    105     |   202     |   0   |
    106     |   203     |   1   |
    107     |   203     |   0   |
    108     |   203     |   0   |

状态:1 = 事件，0 = 不活动

所以我想得到这个:

|Subcategory| Active|Inactive|
------------------------------
|   201     |   3   |   0    |
|   202     |   0   |   2    |
|   203     |   1   |   2    |
|   204     |   0   |   0    |*

请注意，产品表中没有与 SubcategoryId = 204 相关的产品。

我使用此查询仅获取与一个子类别相关的产品。你知道我必须添加什么才能得到“|204|0|0|”行吗？

select p.subcategory,
       sum(case when state = 1 then 1 else 0 end) as active,
       sum(case when state = 0 then 1 else 0 end) as inactive
from product p
group by p.subcategory

Answer 1

使用Left Join从subcategory表中获取所有行，然后执行Count。

SELECT S.subcategoryID,
       Sum(CASE WHEN state = 1 THEN 1 ELSE 0 END) AS active,
       Sum(CASE WHEN state = 0 THEN 1 ELSE 0 END) AS inactive
FROM   subcategory s
       LEFT JOIN product p
              ON s.SubcategoryID = p.Subcategory
GROUP  BY s.subcategoryID 

或者使用Count，可以避免case语句的else部分

SELECT S.subcategoryID,
       count(CASE WHEN state = 1 THEN 1 END) AS active,
       count(CASE WHEN state = 0 THEN 1 END) AS inactive
FROM   subcategory s
       LEFT JOIN product p
              ON s.SubcategoryID = p.Subcategory
GROUP  BY s.subcategoryID 

SQLFIDDLE DEMO