schneerunzel
Sep 21, 2016
view1591
view8
0
Goto Top

Eigenschaften auslesen

GermansolvedQuestionDatabasesDevelopment
Hallo zusammen,

ich fange gerade an mich in ein wenig Reporting einzuarbeiten und stehe jetzt vor folgender Aufgabe:
ich brauche einen Report der eine Fixe anzahl an Eigenschaften darstellt:

Ich brauche folgendes Ergebnis:
+---------------+-----------------+-----------------+-----------------+
|  Person. Nr.  |   Eigenschaft 1 |  Eigenschaft 2  |  Eigenschaft 3  |
+---------------+-----------------+-----------------+-----------------+
|      222      |         X       |         X       |                 |
+---------------+-----------------+-----------------+-----------------+
|      999      |                 |         X       |         X       |                 
+---------------+-----------------+-----------------+-----------------+
Meine Datenbestand sieht wie folgt aus:
+----+----------+-------------+
| id | personr  | eigenschaft |
+----+----------+-------------+
| 59 |      222 |           1 |
| 60 |      222 |           2 |
| 76 |      999 |           2 |
| 64 |      999 |           3 |
+----+----------+-------------+
kann man eine entsprechende SQL abfrage schreiben?

Meine Idee war folgendes:
select (case when eigenschaft = 4 then "X" else " " end)as e1,  
(case when eigenschaft = 5 then "X" else " " end) as e2   
 from eigenschaften
allerdings ergibt das folgendes:
+---------------+-----------------+-----------------+-----------------+
|  Person. Nr.  |   Eigenschaft 1 |  Eigenschaft 2  |  Eigenschaft 3  |
+---------------+-----------------+-----------------+-----------------+
|      222      |         X       |                 |                 |
+---------------+-----------------+-----------------+-----------------+
|      222      |                 |         X       |                 |
+---------------+-----------------+-----------------+-----------------+
|      999      |                 |         X       |                 |                 
+---------------+-----------------+-----------------+-----------------+
|      999      |                 |                 |         X       |                 
+---------------+-----------------+-----------------+-----------------+

hat jemand eine Idee wie man das aufbauen könnte?
comment-contentCommentShareShare
Share on Facebook Share on Twitter Share on Reddit Share on Linkedin

Content-Key: 315853

Url: https://administrator.de/contentid/315853

Printed on: April 16, 2024 at 06:04 o'clock

8 Comments
Latest comment
Goto Top
Member: em-pie
em-pie Sep 21, 2016, updated at Sep 22, 2016 at 11:29:06 (UTC)
Goto Top
Moin,

das sollte funktionieren, wenn du die Tabellen mit Inner join verknüpfst:

Folgender Code ist ungetestet, müsstest beim "where" ggf. mal mit AND und OR spielen
select 
  tbl_a.personr as PersoID
  , tbl_a.eigenschaft as E1
  , tbl_b.eigenschaft as E2
  , tbl_c.eigenschaft as E3
from 
   TABELLE as tbl_a
inner Join TABELLE as tbl_b on 
   tbl_a.peronr = tbl_b.personr
inner Join TABELLE as tbl_c on 
   tbl_a.peronr = tbl_c.personr
where
   tbl_a.eigenschaft='1'  
   and tbl_b.eigenschaft='2'  
   and tbl_c.eigenschaft='3'
NACHTRAG: der Code enthält noch Fehler, eine korrigierte Version (von Biber) findet sich am Fuße des Threads

Ansonsten ist das WWW beim Suchbegriff "SQL ROW to COLUMN" sehr ergiebig.

Gruß
em-pie

P.S.
obiger Code berücksichtig nicht, dass wenn eine PersonalNr. keine Eigenschaft hat, dass diese dennoch angezeigt wird. Hier sollte dir statt Inner join ein Left Join Helfen
Member: Friemler
Solution Friemler Sep 22, 2016 at 00:33:47 (UTC)
Goto Top
Hallo schneerunzel,

mein Vorschlag:
SELECT   personr
         , (CASE WHEN SUM(CASE WHEN eigenschaft = 1 THEN 1 ELSE 0 END) > 0 THEN "X" ELSE " " END) AS e1  
         , (CASE WHEN SUM(CASE WHEN eigenschaft = 2 THEN 1 ELSE 0 END) > 0 THEN "X" ELSE " " END) AS e2  
         , (CASE WHEN SUM(CASE WHEN eigenschaft = 3 THEN 1 ELSE 0 END) > 0 THEN "X" ELSE " " END) AS e3  
FROM     eigenschaften
GROUP BY personr
ORDER BY personr

Gruß
Friemler
Member: schneerunzel
schneerunzel Sep 22, 2016 at 06:16:20 (UTC)
Goto Top
Vielen Dank genau das habe ich gebraucht!!!!!!. Leider hat die erste Lösung in meinem Fall nicht funktioniert.
Member: Biber
Biber Sep 22, 2016 at 08:29:05 (UTC)
Goto Top
Moin schneerunzel,

ich würde auch Friemlers Strategie verwenden, allerdings wegen der Übersichtlichkeit so schreiben:

Select personr, max(e1) as e1, ,max(e2) as e2, max(e3) as e3 From (
SELECT   personr
         , (CASE WHEN eigenschaft = 1 THEN 'X' ELSE " " END) AS e1  
         , (CASE WHEN eigenschaft = 2 THEN 'X' ELSE " " END) AS e2  
         , (CASE WHEN eigenschaft = 3 THEN 'X' ELSE " " END) AS e3  
FROM     eigenschaften
) as whatever
GROUP BY personr
ORDER BY personr

Ist von der Mimik nichts anderes, nur (für mich jedenfalls) lesbarer.

Grüße
Biber
Member: em-pie
em-pie Sep 22, 2016 at 09:46:23 (UTC)
Goto Top
Mein Vorschlag hatte zudem noch einen Bug -.-
und statt Xe sähest du dort dann 1, 2 oder 3 und müsstest du mit CASE-Clauses "umbenennen".

Da die Querys von Friemler und Biber übersichtlicher und somit vermutl. auch performanter sind, wäre dies in der Tat der bevorzugte Weg (welchen du ja auch schon gewählt hast face-smile
Member: Biber
Biber Sep 22, 2016 updated at 10:48:32 (UTC)
Goto Top
Moin em-pie,

das möchte ich nicht so stehen lassen.

Deine Idee, das Ganze über LEFT JOINs lösen zu wolle... na ja, machbar wäre es zwar.
Wenn es um eine Wette ginge oder so - Sinn macht es hier nicht.

Aber wenn es denn sein sollte, dann hat dein Ansatz oben ein paar Probleme:

  • mit einem INNER JOIN würdes du ohnehin nur die Kombinationen bekommen, die alle drei Eigenschaften haben (also keinen Datensatz, da es das nicht gibt in den Spieldaten)

  • aber auch mit einem LEFT JOIN ist doch die "führende Tabelle" bei dir die tbl_a.
tbl_a wird reduziert auf nur die Daten mit "WHERE eigenschaft =1", also bleibt nur die personr 222 übrig.
Da kannst du mit einem LEFT JOIN dranflanschen was du willst... die personr 999 wird nie zur personr 222 passen.

Also brauchst du nicht einen Join über 3 Tabellen (für Eigenschaft 1, 2, 3), sondern musst diese 3 an eine 4te Tabelle knibbeln, in der alle personr einmalig stehen.
select 
  allpersonr.personr as PersoID
  , tbl_a.eigenschaft as E1
  , tbl_b.eigenschaft as E2
  , tbl_c.eigenschaft as E3
from 
 (select distinct personr from eigenschaften) as allpersonr
 Left Join
   (select personr, eigenschaft from eigenschaften where eigenschaft =1) as tbl_a
   on allpersonr.personr = tbl_a.personr
Left Join (select personr, eigenschaft from eigenschaften where eigenschaft =2 ) as tbl_b on 
   allpersonr.personr = tbl_b.personr
   
left Join (select personr, eigenschaft from eigenschaften where eigenschaft =3) as tbl_c on 
   allpersonr.personr = tbl_c.personr
Dann wäre das Ergebnis
PERSOID | E1 | E2 | E3
--------+----+----+---
    222 |  1 |  2 |   
    999 |    |  2 |  3

Bzw, etwas näher an den Vorgaben mit den Xen:
select 
  allpersonr.personr as PersoID
  , tbl_a.E1
  , tbl_b.E2
  , tbl_c.E3
from 
 (select distinct personr from eigenschaften) as allpersonr
 Left Join
   (select personr, 'X' as E1 from eigenschaften where eigenschaft =1) as tbl_a  
   on allpersonr.personr = tbl_a.personr
Left Join (select personr, 'X' as E2 from eigenschaften where eigenschaft =2 ) as tbl_b on   
   allpersonr.personr = tbl_b.personr
   
left Join (select personr, 'X' as E3 from eigenschaften where eigenschaft =3) as tbl_c on   
   allpersonr.personr = tbl_c.personr

Ergebnis:
PERSOID | E1 | E2 | E3
--------+----+----+---
    222 | X  | X  |   
    999 |    | X  | X

Aber das erscheint mir etwas überdimensioniert.

Grüße
Biber
Member: em-pie
em-pie Sep 22, 2016 at 11:27:19 (UTC)
Goto Top
Ich wusste, ich hatte etwas übersehen: Die Tabelle mit den gruppierten/ distincten Personalnummern.

Danke für's gerade ziehen! Hätte es vorher mal schnell testen sollen...
Werde oben mal noch, der Vollständigkeithalber einen Verweis auf deinen Post machen!

Nun gäbe es zwei Versionen, eine komplizierte (meine) und eine (bzw. zwei) schlanke, überschaubare Lösungen (Biber und Friemler).
Member: Biber
Biber Sep 22, 2016 updated at 12:21:53 (UTC)
Goto Top
Moin em-pie,

es gäbe eine weitere Strategie über "Inline-SELECTs", also quasi einen Sub-Select aus einer anderen Tabelle für übereinstimmende Werte mit der führenden Tabelle direkt in der Feldliste:
select 
  a.personr as PersoID
   , (SELECT 'X' From eigenschaften e1 where e1.personr=a.personr and eigenschaft =1) as E1  
   , (SELECT 'X' From eigenschaften e2 where e2.personr=a.personr and eigenschaft =2) as E2  
   , (SELECT 'X' From eigenschaften e3 where e3.personr=a.personr and eigenschaft =3) as E3     
from 
 (select distinct personr from eigenschaften) as a
Resultset ist dann wie vorgegeben und oben abgebildet.
Dieses Vorgehen würde dann Sinn machen, wenn
  • die führende Tabelle, letzte Zeile im Statement (noch) kleiner werden könnte, z.B wenn ich nur auf bestimmte Personen (z.B. aus Abteilung XY) mit einer WHERE-Bedingung einschränken will
  • die Verknüpfung bzw. das WHERE der SubSelects sich auf Attribute bezieht, auf denen ohnehin ein Index liegt, weil das die Schlüsselattribute sind

In dem obigen Datenmodell ist das ja alles Asche - wenn da nicht diese künstliche "ID" als Schlüssel vorhanden wäre, sondern der primary key einfach auf "personr, eigenschaft" läge, dann wäre es sicher die allerschnellste Variante - alle Zugriffe auf die Daten liefen ausschliesslich über Indexfelder und überhaupt nicht auf den Datentabellen. Und ein teures GROUP BY zum Eindampfen des Resultsets ist nicht erforderlich.

Okay, hier bei den 4 Zeilen Beispieldaten ist es egal - von daher nur als ergänzender Kommentar gedacht.

P.S. @schneerunzel
Nein, ich merke gerade, ist mir nicht egal.
Der Aufbau der "Eigenschaften"-Tabelle ist Mist.
Du hast eine ID als Schlüsselattribut, nach dem nie nicht irgendjemand suchen wird und das wahrscheinlich auch in keiner Auswertung/in keinem Bildschirmformular jemals auftaucht.
Zusätzlich hast du das Problem, dass es eigentlich zwei alternative Schlüssel gibt, nämlich die "ID" und die Kombination aus "personr, eigenschaft".
In deinem Datenmodell ist auch nur sichergestellt, dass die ID eindeutig ist - zusätzlich musst du noch per Erfassungslogik oder zusätzliche UNIQUE-Constraint sicherstellen, dass nicht 12 Datensätze mit "Personr=222 und eigenschaft=1" da reindümpeln. Würde ja gehen, die haben alle unterschiedliche IDs.

Ich würde da auf eine künstliche ID verzichten.

Grüße
Biber
GermansolvedQuestionDatabasesDevelopment