计算不同列中相同值的数量

杰克先生0209

我有一个表，我想在其中计算不同列中相同值的数量。但我还不明白如何实现它。请告诉我。

+---------+---------+---------+---------+---------+
| Field1  | Field_A | Field_B | Field_C | Field_D |
+---------+---------+---------+---------+---------+
| Value_A |    0    | 0       | 0       | 0       |
+---------+---------+---------+---------+---------+
| Value_A |    0    | 14      | 0       | 14      |
+---------+---------+---------+---------+---------+
| Value_A |    12   | 0       | 12      | 0       |
+---------+---------+---------+---------+---------+
| Value_A |    0    | 12      | 0       | 0       |
+---------+---------+---------+---------+---------+
| Value_A |    0    | 0       | 0       | 0       |
+---------+---------+---------+---------+---------+
| Value_A |    1    | 12      | 0       | 1       |
+---------+---------+---------+---------+---------+
| Value_A |    0    | 0       | 14      | 0       |
+---------+---------+---------+---------+---------+

最后我想得到以下结果

+---------+-------+-------+
| Field_A | Value | Count |
+---------+-------+-------+
| Value_A | 12    | 4     |
+---------+-------+-------+
| Value_A | 14    | 3     |
+---------+-------+-------+
| Value_A | 1     | 2     |
+---------+-------+-------+

文卡塔拉曼

您可以使用 CROSS APPLY 来实现它

DECLARE @table table (Field1 VARCHAR(10), Field_A int,Field_B int, Field_C int, Field_D int)

INSERT INTO @table 
values
('Value_A',0    , 0       , 0       , 0      ),
('Value_A',0    , 14      , 0       , 14     ),
('Value_A',12   , 0       , 12      , 0      ),
('Value_A',0    , 12      , 0       , 0      ),
('Value_A',0    , 0       , 0       , 0      ),
('Value_A',1    , 12      , 0       , 1      ),
('Value_A',0    , 0       , 14      , 0     );

SELECT Field1,t.Field, count(*) as countOfFields  FROM @table
cross apply
(
VALUES (Field_A),(Field_B), (Field_C),(Field_D)
) as t(Field)
GROUP BY Field1, t.Field

使用 UNPIVOT 的另一种方法

SELECT Field1, val, COUNT(*) FROM @table
unpivot (val for Field in ([Field_A],[Field_B],[Field_C],[Field_D])) AS U
GROUP BY Field1, VAL


+---------+-------+---------------+
| Field1  | Field | countOfFields |
+---------+-------+---------------+
| Value_A |     0 |            19 |
| Value_A |     1 |             2 |
| Value_A |    12 |             4 |
| Value_A |    14 |             3 |
+---------+-------+---------------+

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。