如何计算两个值以任意顺序出现在两列中的次数 [英] How to count the number of times two values appear in two columns in any order

问题描述

可以说，我们有这张桌子:

Lets say, we have this table :

+------+------+
| COL1 | COL2 |
+------+------+
|   A  |   B  |
+------+------+
|   B  |   A  |
+------+------+
|   C  |   D  |
+------+------+

我想计算letter1, letter2或letter2, letter1在两列中出现的次数.

I want to count the number of times either letter1, letter2 or letter2, letter1 appears in the two columns.

我想要结果:

+------+------+------+
| COL1 | COL2 | COL3 |
+------+------+------+
|   A  |   B  |   2  | 
+------+------+------+
|   C  |   D  |   1  |
+------+------+------+

注意:可以是AB或BA无关紧要.

我尝试过:

SELECT
COL1,COL1,COUNT(*) AS COL3
FROM
X
GROUP BY COL1,COL2;

但是那让我:

+------+------+------+
| COL1 | COL2 | COL3 |
+------+------+------+
|   A  |   B  |   1  |
+------+------+------+
|   B  |   A  |   1  |
+------+------+------+
|   C  |   D  |   1  |
+------+------+------+

推荐答案

如果需要，您可以通过交换列来做到这一点:

You can do this by swapping the columns if you need to:

SELECT Col1, Col2, COUNT(*)
FROM
(
    SELECT
        CASE WHEN Col1 < Col2 THEN Col1 ELSE Col2 END AS Col1,
        CASE WHEN Col1 < Col2 THEN Col2 ELSE Col1 END AS Col2
    FROM T
) t
GROUP BY Col1, Col2

小提琴

这篇关于如何计算两个值以任意顺序出现在两列中的次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！