将标题重写为：使用反向值对列进行SQL分组

Question

假设我有一个像这样的表：

+---+---+---+---+---+
|G1 |G2 |G3 |G4 | V |
+---+---+---+---+---+
| A | B | C | D | 2 |
| E | F | G | H | 4 |
| C | D | A | B | 2 |
| E | F | G | H | 3 |
| E | I | G | L | 7 |
+---+---+---+---+---+

其中G1、G3是VARCHAR类型，G2、G4是INT类型

如果我在G1..G4上进行简单的GROUP BY，我会得到：

SELECT G1,G2,G3,G4,SUM(V) as V FROM Table GROUP BY G1, G2, G3, G4;

    +---+---+---+---+---+
    |G1 |G2 |G3 |G4 | V |
    +---+---+---+---+---+
    | A | B | C | D | 2 |
    | E | F | G | H | 7 |
    | C | D | A | B | 2 |
    | E | I | G | L | 7 |
    +---+---+---+---+---+

我想知道是否可能在反转的值G1、G2 <-> G3、G4上进行聚合。 我想要的结果是这样的：

+---+---+---+---+---+      +---+---+---+---+---+
|G1 |G2 |G3 |G4 | V |      |G1 |G2 |G3 |G4 | V |
+---+---+---+---+---+      +---+---+---+---+---+
| A | B | C | D | 4 |  或  | C | D | A | B | 4 |
| E | F | G | H | 7 |      | E | F | G | H | 7 |
| E | I | G | L | 7 |      | E | I | G | L | 7 |
+---+---+---+---+---+      +---+---+---+---+---+

我尝试获取反转的行，但由于重复，我仍然无法在它们上进行聚合。我的测试查询是：

SELECT DISTINCT *
FROM Table t1
JOIN Table t2 on t1.G1 = t2.G3 and t1.G2=t2.G4 and t1.G3=t2.G1 and t1.G4=t2.G2

    +---+---+---+---+---+---+---+---+---+---+
    |G1 |G2 |G3 |G4 | V |G1 |G2 |G3 |G4 | V |
    +---+---+---+---+---+---+---+---+---+---+
    | A | B | C | D | 2 | C | D | A | B | 2 |
    | C | D | A | B | 2 | A | B | C | D | 2 |
    +---+---+---+---+---+---+---+---+---+---+

Answer 1

如果我理解正确，将列的顺序反转，使得 A B C D 对应 C D A B。

select 
         case when(g1 > g3) then g3 else g1 end G1,
         case when(g1 > g3) then g4 else g2 end G2,
         case when(g1 > g3) then g1 else g3 end G3,
         case when(g1 > g3) then g2 else g4 end G4, sum(V)
from tbl
group by case when(g1 > g3) then g3 else g1 end,
         case when(g1 > g3) then g4 else g2 end,
         case when(g1 > g3) then g1 else g3 end,
         case when(g1 > g3) then g2 else g4 end

db<>fiddle