Gegroepeerde records optellen, deze daarna van een andere record aftrekken
Jaja het is me de titel wel, maar dit omschrijft wel wat ik wil.
Ik heb een tabel artikel_per_order. Hierin zitten onder meer de volgende velden:
apo_ID (unieke ID)
artikel_ID (verwijzing naar artikel_ID in tabel artikelen)
order_ID (verwijzing naar order_ID in tabel orders)
apo_aantal (aantal bestelde artikelen)
En ik heb een tabel artikel_per_pakbon. Hierin zitten de volgende velden:
app_ID (unieke ID)
apo_ID (verwijzing naar apo_ID in tabel artikel_per_order)
pakbon_ID (verwijzing naar pakbon_ID in tabel pakbonnen)
app_geleverd (aantal geleverde artikelen.(voor een deellevering))
Nu wil ik een tabel genereren waarin alle openstaande artikelen komen te staan waarvan nog een pakbon gemaakt kan worden, gegroepeerd per order. Dit betreft de artikelen waarvan nog niets is uitgeleverd en artikelen waarvan al een gedeelte is uitgeleverd.
Voorheen deed ik dat, vond ik makkelijker, d.m.v. php while lusjes. Natuurlijk weet ik dat dat niet de manier is, maar het werkte. Maar nu k met classes wil gaan werken kom ik er eigenlijk steeds meer achter dat dat helemaal niet zo handig is.
Ik ben me dus maar weer een tijd je aan het focussen om dit op SQL niveau op te lossen.
Ik ben al een heel eind, maar ik krijg te weinig records terug:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT app.apo_ID, SUM( app.app_geleverd ) AS totaal, (
SUM( app.app_geleverd ) - apo.apo_aantal
) AS resterend, apo.apo_aantal
FROM artikel_per_pakbon AS app
INNER JOIN artikel_per_order AS apo ON app.apo_ID = apo.apo_ID
GROUP BY app.apo_ID
HAVING totaal<apo.apo_aantal
SUM( app.app_geleverd ) - apo.apo_aantal
) AS resterend, apo.apo_aantal
FROM artikel_per_pakbon AS app
INNER JOIN artikel_per_order AS apo ON app.apo_ID = apo.apo_ID
GROUP BY app.apo_ID
HAVING totaal<apo.apo_aantal
Heeft iemand een voorzetje ;-)
Groet Johan
Wat je nodig hebt is een LEFT JOIN, en omdat je het aantal bestelde wilt afzetten tegen het aantal te leveren moet je beginnen met de artikelen per bestelling:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
order_id,
artikel_id,
a.artikel_naam,
o.aantal_besteld - COALESCE(SUM(p.geleverd), 0) te_leveren
FROM
artikelen_per_order o
JOIN artikelen a
USING (artikel_id)
LEFT JOIN
artikelen_per_pakbon p
USING (order_id, artikel_id)
GROUP BY order_id, artikel_id
HAVING te_leveren > 0
order_id,
artikel_id,
a.artikel_naam,
o.aantal_besteld - COALESCE(SUM(p.geleverd), 0) te_leveren
FROM
artikelen_per_order o
JOIN artikelen a
USING (artikel_id)
LEFT JOIN
artikelen_per_pakbon p
USING (order_id, artikel_id)
GROUP BY order_id, artikel_id
HAVING te_leveren > 0
De auto increment id in je artikelen_per_order tabel is zinloos, je creëert daarmee een niet bestaande relatie. De relatie is order_id met de order tabel en artikel_id met de artikelen tabel.
Voor de pakbonnen geldt hetzelfde, echte kan je hier overwegen om een surrogaat PK te gebruiken.
Gewijzigd op 20/03/2015 22:45:10 door Ger van Steenderen
Ik begrijp alleen niet waarom je mijn ID in de tabel artikel_per_order zinloos vind.
OP de eerste plaatst dacht ik dat een tabel altijd een unieke, primary key moest hebben.
Verder gebruik ik de ID om in de artikel_per_pakbon op te slaan.
Ook gebruik ik deze om bijvoorbeeld iets te wijzigen aan het artikel in de betreffende order.
Anders zou ik twee keys nodig hebben.
Of zie ik dat verkeerd?
Ik heb jouw code even aangepast en hij werkt:
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
SELECT o.order_ID, o.artikel_ID, a.artikel_naam, o.apo_aantal - COALESCE( SUM( p.app_geleverd ) , 0 ) te_leveren
FROM artikel_per_order o
JOIN artikelen a
USING ( artikel_ID )
LEFT JOIN artikel_per_pakbon p
USING ( apo_ID )
GROUP BY o.order_ID, o.artikel_ID
HAVING te_leveren >0
FROM artikel_per_order o
JOIN artikelen a
USING ( artikel_ID )
LEFT JOIN artikel_per_pakbon p
USING ( apo_ID )
GROUP BY o.order_ID, o.artikel_ID
HAVING te_leveren >0
Ik ben ook nog verder aan het zoeken geweest en kwam aan de volgende query:
Code (php)
1
2
3
4
5
2
3
4
5
SELECT apo.apo_ID, apo.apo_aantal, SUM( app.app_geleverd ) AS totaal
FROM artikel_per_order AS apo
LEFT OUTER JOIN artikel_per_pakbon AS app ON ( app.apo_ID = apo.apo_ID )
GROUP BY apo.apo_ID, apo.apo_aantal
HAVING COALESCE( SUM( app.app_geleverd ) , 0 ) != apo.apo_aantal
FROM artikel_per_order AS apo
LEFT OUTER JOIN artikel_per_pakbon AS app ON ( app.apo_ID = apo.apo_ID )
GROUP BY apo.apo_ID, apo.apo_aantal
HAVING COALESCE( SUM( app.app_geleverd ) , 0 ) != apo.apo_aantal
Beide query's lijken te werken, alleen ze duren allebei verschrikkelijk lang. Zeg maar ongeveer 4,5 minuut.
De artikel_per_order tabel bevat 7910 records
De artikel_per_pakbon tabel bevat 8087 records
Ik had verwacht dat een rechtstreekse query sneller was dan php-while lusjes zoals ik het voorheen deed.
Toen duurde het hooguit enkele seconden.
Johan Rombouts op 22/03/2015 14:12:48:
Bedankt voor de reactie, Ger.
Ik begrijp alleen niet waarom je mijn ID in de tabel artikel_per_order zinloos vind.
OP de eerste plaatst dacht ik dat een tabel altijd een unieke, primary key moest hebben.
Verder gebruik ik de ID om in de artikel_per_pakbon op te slaan.
Ook gebruik ik deze om bijvoorbeeld iets te wijzigen aan het artikel in de betreffende order.
Anders zou ik twee keys nodig hebben.
Of zie ik dat verkeerd?
Ik begrijp alleen niet waarom je mijn ID in de tabel artikel_per_order zinloos vind.
OP de eerste plaatst dacht ik dat een tabel altijd een unieke, primary key moest hebben.
Verder gebruik ik de ID om in de artikel_per_pakbon op te slaan.
Ook gebruik ik deze om bijvoorbeeld iets te wijzigen aan het artikel in de betreffende order.
Anders zou ik twee keys nodig hebben.
Of zie ik dat verkeerd?
De definitie van een primary key is dat ie uniek moet zijn en niet leeg (NULL) mag zijn. Nergens staat beschreven dat het maar één kolom kan zijn, en nog minder dat het een automatische nummering moet zijn.
Omdat je telkens toch al het order_id en/of het artikel_id tot je beschikking hebt is een automatisch genummerde kolom overbodig, sterker nog dit vergt alleen maar meer overhead.
Als je query te langzaam is moet je eerst kijken of alle kolommen die refereren naar een andere tabel geindexed zijn.
Daarnaast is het zo dat van beide tabellen alle records nodig zijn (eerst wordt verzameld dan gegroepeerd) dus de snelste query zal het nooit worden. Echter viereneenhalve minuut is wel heel erg traag, dus ik vermoed een ontbrekende index.
Dankzij je tip en deze tut http://www.phphulp.nl/php/tutorial/overig/mysql-indexes/545/ van Joren de Wit duurt de query nu euhm... 0.12 seconde.
Door een simpele index op apo_ID in de tabel artikel_per_pakbon.
In principe dus opgelost!!! Nogmaals dank!
Maar ben toch wel benieuwd, Ger, hoe jij het oplost als je meerdere van dezelfde artikel in 1 order hebt zitten. (bijvoorbeeld omdat bij de 1 net een andere omschrijving moet of zo). Je kunt dan niet meer zeggen dat als je zoekt op artikel_ID en order_ID dat je dan maar 1 rij krijgt.
Gewijzigd op 24/03/2015 12:12:19 door Ger van Steenderen
select hoog_tarief from tabel stroom
row - previousrow = result.
o nieuw topic
Gewijzigd op 31/07/2015 18:30:55 door arnaud feith