za neke stvari je mysql keva, al za neke je gluuuuuuuuuuuuuuuuuuup .. posebno mu optimizer po nekad nije jaca strana ...
umesto
Code:
SUM(aprimke.kolicina - anormativ.kolicina - aracuni.kolicina)
ovo moze da bude i do 10 puta brze !! (nekad nece biti brze uopste, ali vrlo cesto hoce)
Code:
SUM(aprimke.kolicina) - SUM(anormativ.kolicina) - SUM(aracuni.kolicina)
e sad .. sam upit .. ja se u knjigovodstvo razumem malo manje nego u strikanje (za strikanje znam da postoje neki klot i neki frket) tako da nemam pojma sta racunas ali posto koristis INNER JOIN, ako u bilo kojoj tabeli nema nekog artikla, neces ga imati ni u razultatu ... dakle ako u tabeli anormativ nemas artikli_artikl_id za vrednost 10, nebitno sto postoji i u artikli i u aprimke i u aracuni, nece se pojaviti u ovom izvestaju.
dakle:
Code:
mysql> select * from artikli;
+-----------+-------+-------+
| artikl_id | naziv | sifra |
+-----------+-------+-------+
| 1 | pera | 1 |
| 2 | zika | 2 |
| 3 | mika | 3 |
+-----------+-------+-------+
3 rows in set (0.00 sec)
mysql> select * from aprimke;
+-------------------+----------+
| artikli_artikl_id | kolicina |
+-------------------+----------+
| 1 | 5 |
| 1 | 5 |
| 1 | 7 |
| 2 | 5 |
+-------------------+----------+
4 rows in set (0.00 sec)
mysql> select * from aracuni;
+-------------------+----------+
| artikli_artikl_id | kolicina |
+-------------------+----------+
| 1 | 5 |
| 1 | 5 |
| 1 | 7 |
| 2 | 8 |
| 3 | 5 |
| 3 | 233 |
+-------------------+----------+
6 rows in set (0.00 sec)
mysql> select * from anormativ;
+-------------------+----------+
| artikli_artikl_id | kolicina |
+-------------------+----------+
| 1 | 5 |
| 1 | 7 |
| 2 | 8 |
+-------------------+----------+
3 rows in set (0.00 sec)
mysql> -- iz upita izbacimo group by i sume zamenimo sa vrednostima da bi videli nad kojim setom radimo
mysql> SELECT artikli.naziv, artikli.sifra, aprimke.kolicina,anormativ.kolicina,aracuni.kolicina
-> FROM artikli
-> INNER JOIN aprimke ON aprimke.artikli_artikl_id = artikli.artikl_id
-> INNER JOIN aracuni ON aracuni.artikli_artikl_id = artikli.artikl_id
-> INNER JOIN anormativ ON anormativ.artikli_artikl_id = artikli.artikl_id
-> ;
+-------+-------+----------+----------+----------+
| naziv | sifra | kolicina | kolicina | kolicina |
+-------+-------+----------+----------+----------+
| pera | 1 | 5 | 5 | 5 |
| pera | 1 | 5 | 7 | 5 |
| pera | 1 | 5 | 5 | 5 |
| pera | 1 | 5 | 7 | 5 |
| pera | 1 | 7 | 5 | 5 |
| pera | 1 | 7 | 7 | 5 |
| pera | 1 | 5 | 5 | 5 |
| pera | 1 | 5 | 7 | 5 |
| pera | 1 | 5 | 5 | 5 |
| pera | 1 | 5 | 7 | 5 |
| pera | 1 | 7 | 5 | 5 |
| pera | 1 | 7 | 7 | 5 |
| pera | 1 | 5 | 5 | 7 |
| pera | 1 | 5 | 7 | 7 |
| pera | 1 | 5 | 5 | 7 |
| pera | 1 | 5 | 7 | 7 |
| pera | 1 | 7 | 5 | 7 |
| pera | 1 | 7 | 7 | 7 |
| zika | 2 | 5 | 8 | 8 |
+-------+-------+----------+----------+----------+
19 rows in set (0.00 sec)
mysql>
ako je to ono sto hoces - super ... ali ono sto je verovatnije da ti treba je
Code:
mysql> SELECT artikli.naziv, artikli.sifra, aprimke.kolicina,anormativ.kolicina,aracuni.kolicina
-> FROM artikli
-> LEFT JOIN aprimke ON aprimke.artikli_artikl_id = artikli.artikl_id
-> LEFT JOIN aracuni ON aracuni.artikli_artikl_id = artikli.artikl_id
-> LEFT JOIN anormativ ON anormativ.artikli_artikl_id = artikli.artikl_id
-> ;
+-------+-------+----------+----------+----------+
| naziv | sifra | kolicina | kolicina | kolicina |
+-------+-------+----------+----------+----------+
| pera | 1 | 5 | 5 | 5 |
| pera | 1 | 5 | 7 | 5 |
| pera | 1 | 5 | 5 | 5 |
| pera | 1 | 5 | 7 | 5 |
| pera | 1 | 5 | 5 | 7 |
| pera | 1 | 5 | 7 | 7 |
| pera | 1 | 5 | 5 | 5 |
| pera | 1 | 5 | 7 | 5 |
| pera | 1 | 5 | 5 | 5 |
| pera | 1 | 5 | 7 | 5 |
| pera | 1 | 5 | 5 | 7 |
| pera | 1 | 5 | 7 | 7 |
| pera | 1 | 7 | 5 | 5 |
| pera | 1 | 7 | 7 | 5 |
| pera | 1 | 7 | 5 | 5 |
| pera | 1 | 7 | 7 | 5 |
| pera | 1 | 7 | 5 | 7 |
| pera | 1 | 7 | 7 | 7 |
| zika | 2 | 5 | 8 | 8 |
| mika | 3 | NULL | NULL | 5 |
| mika | 3 | NULL | NULL | 233 |
+-------+-------+----------+----------+----------+
21 rows in set (0.00 sec)
mysql>
to jest
Code:
mysql> SELECT artikli.naziv, artikli.sifra,
-> SUM(aprimke.kolicina)-SUM(anormativ.kolicina)-SUM(aracuni.kolicina) as stagod
-> FROM artikli
-> LEFT JOIN aprimke ON aprimke.artikli_artikl_id = artikli.artikl_id
-> LEFT JOIN aracuni ON aracuni.artikli_artikl_id = artikli.artikl_id
-> LEFT JOIN anormativ ON anormativ.artikli_artikl_id = artikli.artikl_id
-> GROUP BY artikli.artikl_id
-> ;
+-------+-------+--------+
| naziv | sifra | stagod |
+-------+-------+--------+
| pera | 1 | -108 |
| zika | 2 | -11 |
| mika | 3 | NULL |
+-------+-------+--------+
3 rows in set (0.00 sec)
mysql>
gde pretpostavljam da zelis da ako fali nesto dobijes null - posto to znaci gresku (fali ti negde neki upis), no, mozes i to da sredis sa
Code:
mysql> SELECT artikli.naziv, artikli.sifra,
-> SUM(COALESCE(aprimke.kolicina,0))-SUM(COALESCE(anormativ.kolicina,0))-SUM(COALESCE(aracuni.kolicina,0)) as stagod
-> FROM artikli
-> LEFT JOIN aprimke ON aprimke.artikli_artikl_id = artikli.artikl_id
-> LEFT JOIN aracuni ON aracuni.artikli_artikl_id = artikli.artikl_id
-> LEFT JOIN anormativ ON anormativ.artikli_artikl_id = artikli.artikl_id
-> GROUP BY artikli.artikl_id
-> ;
+-------+-------+--------+
| naziv | sifra | stagod |
+-------+-------+--------+
| pera | 1 | -108 |
| zika | 2 | -11 |
| mika | 3 | -238 |
+-------+-------+--------+
3 rows in set (0.00 sec)
mysql>
ovde ce za svaku nepostojecu vrednost da podrazumeva 0
nadam se da je jasno