Ich habe diese beiden Tabellen:

TableA:
aid ! name
--- ! -----
1   ! Paper
2   ! Rock
3   ! Sciz

Table B (aid is TableA FKEY)
id ! aid ! area  ! mode
-- ! --- ! ----- ! ----
1  ! 1   ! 200   ! 1
2  ! 1   ! 240   ! 2
3  ! 2   ! 300   ! 1
4  ! 2   ! 290   ! 2
5  ! 3   ! 100   ! 1
6  ! 3   ! 110   ! 2

Ich möchte eine Abfrage schreiben, die die folgenden Ergebnisse zurückgibt:

aid ! area(mode=1) ! area(mode=2) 
--- ! ------------ ! ------------
1   ! 200          ! 240
2   ! 300          ! 290
3   ! 100          ! 110

Wie kann ich dies in mySQL erreichen? Ich muss auch die Fälle ausschließen, in denen Tabelle B nur einen Wert für mode1 und nicht für mode2 enthält und umgekehrt.

Vielen Dank.

0
MobileCushion 17 Apr. 2018 im 17:10

5 Antworten

Beste Antwort

Dies ist ein Prozess, der als Pivot bezeichnet wird.
Und kann mit GROUP BY in Kombination mit MAX(CASE END) -Klauseln durchgeführt werden.
Die HAVING-Klausel stellt sicher, dass ein B.aid beide Datensätze enthält, die den Modus 1 und 2 enthalten.

SELECT 
   B.aid 
 , MAX(CASE WHEN B.mode = 1 WHEN B.area ELSE 0 END) AS 'area(mode=1)'
 , MAX(CASE WHEN B.mode = 2 WHEN B.area ELSE 0 END) AS 'area(mode=2)'
FROM 
 B
GROUP BY
 B.aid 
HAVING
   SUM(B.mode = 1) = 1
 AND
   SUM(B.mode = 2) = 1
ORDER BY 
 B.aid ASC
2
Raymond Nijland 17 Apr. 2018 im 14:44

http://sqlfiddle.com/#!9/2db6c5/1

Versuche dies;

SELECT b1 .aid, b1.area, b2.area  
FROM tablea a1
LEFT JOIN tableb b1 ON a1.aid = b1 .aid
LEFT JOIN tableb b2 ON a1.aid = b2 .aid
where 
b1.mode = 1
and b2.mode = 2
2
Pelin 17 Apr. 2018 im 14:29

Wenn Sie beide Modi benötigen, würde ich die Verwendung von inner join vorschlagen:

SELECT a.aid, b1.area, b2.area  
FROM tablea a1 JOIN
     tableb b1
     ON a1.aid = b1.aid AND b1.mode = 1 JOIN
     tableb b2
     ON a1.aid = b2.aid AND b2.mode = 2;

Der innere Join stellt sicher, dass beide Werte in b vorhanden sind.

Da beide Modi vorhanden sein müssen, benötigen Sie tablea nicht:

SELECT b1.aid, b1.area, b2.area  
FROM tableb b1
     ON a1.aid = b1.aid JOIN
     tableb b2
     ON a1.aid = b2.aid AND b1.mode = 1  AND b2.mode = 2;

Wenn Sie nur die aid benötigen, in denen beide vorhanden sind, können Sie auch Folgendes tun:

select b.aid, max(case when mode = 1 then area end) as area1,
       max(case when mode = 2 then area end) as area2
from tableb b
group by b.aid
having area1 is not null and area2 is not null;

Dies erfordert überhaupt kein Verbinden der Tabellen.

2
Gordon Linoff 17 Apr. 2018 im 14:37

Ich weiß nicht, warum Tabelle A den Zweck von Tabelle A hat. Aber ich denke, es wird der Ausgangspunkt sein.

SELECT DISTINCT a.aid
    ,b1.area as area_mode1
    ,b2.area as area_mode2

FROM TableA a
    JOIN TableB b1 ON (a.aid = b1.aid AND b1.mode = '1')
    JOIN TableB b2 ON (a.aid = b2.aid AND b2.mode = '2')
;

Vielleicht macht auch ein SUBSELECT den Trick, aber ich denke, der doppelte JOIN von Tabelle B kostet weniger Speicher für größere Tabellen.

Hinweis: Sie sollten Feldnamen wie 'Name' und 'Modus' vermeiden. Sie sind reservierte Wörter in SQL und können zu verwirrenden Ergebnissen führen.

1
LupusE 17 Apr. 2018 im 14:46

Das sollte funktionieren:

select * from(
    select aid, max(case when mode = 1 then area end) as mode1, 
                max(case when mode = 2 then area end) as mode2
    from tableB  
    group by aid order by aid
)where mode1 is not null and mode2 is not null
1
isaace 17 Apr. 2018 im 19:10