mysql - 如何计算两个值以任意顺序出现在两列中的次数

Question

比方说，我们有这张 table :

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

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

我想要结果:

+------+------+------+
| 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  |
+------+------+------+

Answer 1

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

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

Fiddle