Andaikan saya mempunyai jadual seperti ini:
+---+---+---+---+---+ |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 dan G3 ialah jenis VARCHAR, G2 dan G4 ialah jenis INT
Jika saya melakukan GROUP BY yang mudah pada G1..G4, saya mendapat:
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 |
+---+---+---+---+---+
Saya ingin tahu sama ada boleh melakukan pengagregatan pada nilai terbalik G1, G2 <-> G3, G4. Hasil yang saya mahukan ialah:
+---+---+---+---+---+ +---+---+---+---+---+ |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 | +---+---+---+---+---+ +---+---+---+---+---+
Saya cuba mendapatkan baris terbalik tetapi saya masih tidak dapat mengagregatkannya kerana pendua. Pertanyaan ujian saya ialah:
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 |
+---+---+---+---+---+---+---+---+---+---+
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Jika saya faham dengan betul, terbalikkan susunan lajur supaya A B C D sepadan dengan 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 enddb<>fiddle