Erklärung Tabelle3:
Linda zieht am 10.1. in Stall 1
Linda zieht am 11.1. in Stall 2 Berta zieht am 11.1. in Stall 1
Linda zieht am 12.1. in Stall 1
Linda zieht am 13.1. in Stall 2
Siggi zieht am 14.1. in Stall 1
Nun möchte ich wissen, wie viele Tiere am 2017-01-12 im Stall1 waren:
select count(animal.a_id) from Animal as animal where animal.a_id IN (select removal.re_a_id from Removal as removal where re_s_id = 1)
Ich bekomme nachtürlich 3 zurück, weil ich nicht weiß, wie ich es mit dem Datum lösen kann.
Als Antwort hätte ich gerne 2 (die 2 Fett-gedruckten)
test=*# select re_a_id, re_s_id, max(re_date) from removals where re_date <= '2017-01-12' and re_s_id = 1 group by re_a_id, re_s_id ;
re_a_id | re_s_id | max
---------+---------+------------
1 | 1 | 2017-01-12
2 | 1 | 2017-01-11
(2 Zeilen)
Das joinen der anderen Tabellen überlasse ich Dir zur Übung
die reine Anzahl:
test=*# select count(1) from (select re_a_id, re_s_id, max(re_date) from removals where re_date <= '2017-01-12' and re_s_id = 1 group by re_a_id, re_s_id ) foo;
count
-------
2
(1 Zeile)
select re_date, re_a_id, re_s_id from (select *, row_number() over (partition by re_a_id order by re_date desc) as current_place from removals where re_date <= '2017-01-12' order by re_a_id, re_date) foo where current_place = 1 and re_s_id = 1;
und neu (so funktioniert’s):
select re_date, re_a_id, re_s_id from (select *, last_value(re_s_id) over (partition by re_a_id order by re_date desc) as current_place from removals where re_date <= '2017-01-12' order by re_a_id, re_date) foo where current_place = 1 and re_s_id = 1;