Esame 2002 – Banca del tempo query 1
hadato haric
datad ored codicepd codicead
NULL 3 1 2
NULL 2 2 2
NULL 2 3 3
NULL 5 1 3
select codicepd, sum(ored) as credito from `1hadato` group by codicepd
codicepd credito
1 8
2 2
3 2
select codicepr, sum(orer) as debito from `1haric` group by codicepr
codicepr debito
3 4
4 1
5 6
select * from
(select codicepd, sum(ored) as credito from `1hadato` group by codicepd) as d left join
(select codicepr, sum(orer) as debito from `1haric` group by codicepr) as r on d.codicepd=r.codicepr
codicepd credito codicepr debito 1 8 NULL NULL 2 2 NULL NULL
3 2 3 4
select * from
(select codicepd, sum(ored) as credito from `1hadato` group by codicepd) as d right join
(select codicepr, sum(orer) as debito from `1haric` group by codicepr) as r on d.codicepd=r.codicepr
codicepd credito codicepr debito
3 2 3 4
NULL NULL 4 1 NULL NULL 5 6
datar orer codicepr codicear
NULL 4 3 1
NULL 1 4 2
NULL 6 5 3
select * from
(select codicepd, sum(ored) as credito from `1hadato` group by codicepd) as d left join
(select codicepr, sum(orer) as debito from `1haric` group by codicepr) as r on d.codicepd=r.codicepr
union
select * from
(select codicepd, sum(ored) as credito from `1hadato` group by codicepd) as d right join
(select codicepr, sum(orer) as debito from `1haric` group by codicepr) as r on d.codicepd=r.codicepr
codicepd credito codicepr debito 1 8 NULL NULL 2 2 NULL NULL
3 2 3 4
NULL NULL 4 1 NULL NULL 5 6
select
if (temp.codicepd,temp.codicepd,temp.codicepr) as codice, case
when temp.credito is not NULL and temp.debito is not NULL Then temp.credito temp.debito when temp.credito is NULL Then temp.debito
else temp.credito end as bilancio from
(select * from
(select codicepd, sum(ored) as credito from `1hadato` group by codicepd) as d left join
(select codicepr, sum(orer) as debito from `1haric` group by codicepr) as r on d.codicepd=r.codicepr
union
select * from
(select codicepd, sum(ored) as credito from `1hadato` group by codicepd) as d right join
(select codicepr, sum(orer) as debito from `1haric` group by codicepr) as r on d.codicepd=r.codicepr
) as temp
codice bilancio
1 8
2 2
3 -2
4 -1
5 -6
