Auf Vollständigkeit über Jahre prüfen

[Denfroe]

Hallo zusammen,

ich bin neu hier und bei SQL ein kompletter Anfänger. Im Rahmen meiner Masterarbeit muss ich mich mir der Thematik von SQL auseinandersetzten und eine Datenbank bearbeiten.

Die Tabelle hat den folgenden Aufbau:
Krankenhausidentidentifikationsnummer (kh_iknr) | jahr | Standortnummer (kh_standnr)

Mein Problem ist folgendes:
Ich möchte gerne nur die Krankenhäuser ausbekommen, die jeweils in den Jahren 2006,2008,2010,2012,2013 einen Eintrag haben und die Standortnummer 0 haben.

Ich habe schon probiert den Code zu schreiben und dabei kam folgendes raus:
SELECT * FROM quali.kh_uid as x
where kh_standnr = 0 and
exists (select * from quali.kh_uid as a
where x.kh_iknr=a.kh_iknr and jahr = 2006) and
exists (select * from quali.kh_uid as b
where x.kh_iknr=b.kh_iknr and jahr = 2008) and
exists (select * from quali.kh_uid as c
where x.kh_iknr=c.kh_iknr and jahr = 2010) and
exists (select * from quali.kh_uid as d
where x.kh_iknr=d.kh_iknr and jahr = 2012) and
exists (select * from quali.kh_uid as e
where x.kh_iknr=e.kh_iknr and jahr = 2013);

Das Problem hierbei ist leider, dass ich auch Einträge bekomme, die nur zwei aufeinanderfolgende Jahre haben z.B. (2006 und 2008).
Ich möchte aber nur Krankenhäuser haben, wo die kh_iknr in allen Jahren auftaucht.

Kann mir da jemand weiter helfen?

Schon mal vielen Dank im Voraus.

 

[akretschmer]

mal vereinfach, du hast:

test=*# select * from denfroe ;
 id | jahr
----+------
  1 | 2006
  1 | 2008
  1 | 2013
  2 | 2006
  2 | 2008
  2 | 2010
  2 | 2012
  2 | 2013
  3 | 2010
  3 | 2011
  3 | 2012
(11 rows)

Nur die id = 2 hat Einträge in allen von Dir genanntenJahren, das finden wir so:

test=*# select id from (select id, array_agg(jahr) as jahre from denfroe group by id) foo where jahre @> array[2006,2008,2010,2012,2013];
 id
----
  2
(1 row)

Das ist eine Lösung mit PostgreSQL. Ich fürchte aber, MySQL wird das nicht können.

 

[akretschmer]

Das könnte vielleicht mit viel Glück inMySQL gehen:

test=*# select id, sum(1) as anzahl from denfroe where jahr in (2006,2008,2010,2012,2013) group by id having sum(1) = 5;
 id | anzahl
----+--------
  2 |  5
(1 row)

Ist halt mit MySQL immer so eine Sache, weil massiv ohne Features und so...

 

[Denfroe]

Vielen dank für deine Antwort.
Ich werde das gleich mal ausprobieren.

 

[Denfroe]

Es hat funktioniert. Ich habe die id raus bekommen.

Bekommt man das vielleicht aus so hin, dass die Ausgangstabelle in ihrem Format gleich bleibt, d.h. das die verschiedenen Jahre noch aufgeführt werden?

 

[akretschmer]

Dein Freund JOIN wohnt gleich um die Ecke rum ;-)

 

[Denfroe]

Vielen Dank für den Tipp.
Ich weiß nur gerade nur leider nicht, wie ich am besten das umsetzte
Bringe mir SQL erst seit ein paar Tagen bei.

Schreibe ich das einfach hinter den Code mit einem INNER JOIN?

SELECT kh_iknr, sum(1) as anzahl FROM quali.kh_uid
where kh_standnr = 0 and
jahr in(2006,2008,2010,2012,2013)
group by kh_iknr
having sum(1) = 5

........;

 

[akretschmer]

test=*# select * from denfroe right join (select id from (select id, array_agg(jahr) as jahre from denfroe group by id) foo where jahre @> array[2006,2008,2010,2012,2013]) bla using (id);
 id | jahr
----+------
  2 | 2006
  2 | 2008
  2 | 2010
  2 | 2012
  2 | 2013
(5 rows)

 

[Denfroe]

Danke für die Antwort.
Kann ich mit diesem Code in MYSQL arbeiten?

 

[akretschmer]

versuch es ;-)

Das ist im Subquery die Version, die nicht geht. Das gegen die mit MySchrott funktionierende Version zu tauschen überlasse ich Dir zur Übung ;-)

 

[Denfroe]

OK
Ich probiere mein bestes

 

[akretschmer]

du schaffst das ;-)

 

[Denfroe]

Ich verstehe dein Code soweit und habe das umgesetzt.

Für mich ist nur der letzte Teil unklar.
test=*# select * from denfroe right join (select id from (select id, array_agg(jahr) as jahre from denfroe group by id) foo where jahre @> array[2006,2008,2010,2012,2013]) bla using (id);

Ich sage, ja, dass er die id aus der Anfrage nehmen soll und sie mit einem right join mit der Ausgangstabelle verbinden soll.

select * from quali.kh_uid right join
(select kh_iknr from (
SELECT kh_iknr, sum(1) as anzahl FROM quali.kh_uid
where kh_standnr = 0 and
jahr in(2006,2008,2010,2012,2013)
group by kh_iknr
having sum(1) = 5));

 

[Denfroe]

habe es doch noch hinbekommen.
Vielen dank nochmal akretschmer.