sql - SQL 按多个分类列分组并计数

Question

我有 3 个具有相同值范围的分类列：0/1 和 NULL。

SQL 中的列：

第 1 列	第 2 列	第 3 列
0	1	1
0	1	0
1	无效的	1
无效的	0	1

我想要这样的每个类别的计数：

类别	Cnt_Col1	Cnt_Col2	Cnt_Col3
0	2	1	1
1	1	2	3
无效的	1	1	0

有人知道这在 SQL 中是如何实现的吗？以下查询未给出预期结果：

按 column1、column2、column3 从表组中选择 count(*)、column1、column2、column3

标签： sql

Answer 1

我认为你想取消透视然后聚合。在通用 SQL 中，您可以使用：

select category,
       sum(case when which = 'column1' then 1 else 0 end) as cnt_col1,
       sum(case when which = 'column2' then 1 else 0 end) as cnt_col2,
       sum(case when which = 'column3' then 1 else 0 end) as cnt_col3
from ((select column1 as category, 'column1' as which from t) union all
      (select column2 as category, 'column2' as which from t) union all
      (select column3 as category, 'column3' as which from t) 
     ) c
group by category;

在 SQL Server 中，我建议APPLY取消透视：

select v.category,
       sum(case when v.which = 'column1' then 1 else 0 end) as cnt_col1,
       sum(case when v.which = 'column2' then 1 else 0 end) as cnt_col2,
       sum(case when v.which = 'column3' then 1 else 0 end) as cnt_col3
from t cross apply
     (values (column1, 'column1'),
             (column2, 'column2'),
             (column3, 'column3')
     ) v(category, which)
group by v.category;