ugrás a tartalomhoz

Két tábla JOIN-olt lekérdezésben különböző ORDER-rel.

s_volenszki · 2008. Szep. 7. (V), 14.14
Sziasztok!

Sgítsetek nekem egy kicsit, nem értek valamit és kellene rá egy lekérdezést írnom:

Van két táblám pl.: emberek és esemenyek.

Az emberek táblában értelem szerűen emberek neveit tárolom az esemenyek táblában pedig az emberekhez tartozó eseményeket, azaz egy 1:n azonosított kapcsolatról beszélek.

Az események dátum szerint kerülnek bejegyzésre, itt egy klasszikus példa:

1 rekord az emberek táblában:
emberek_id: 1
emberek_nev: Minta Péter

Az előbb leírt rekordhoz tartozó 1. esemény az esemenyek táblában:
esemenyek_id: 1250
esemenyek_emberek_id: 1
esemeny_datuma: 1742
esemeny_leirasa: Születés

Az előbb leírt rekordhoz tartozó 2. esemény az esemenyek táblában:
esemenyek_id: 3500
esemenyek_emberek_id: 1
esemeny_datuma: 1770
esemeny_leirasa: Halálozás


Úgy kellene lekérdeznem, hogy az emberek kellenek és mindegyikhez az időben legutolsó esemény.

Ez majdnem jó, csak az események közül nem a legutolsóval tér vissza:

SELECT * FROM `enberek` JOIN `esemenyek` ON esemenyek.esemenyek_emberek_id = emberek.emberek_id GROUP BY esemenyek_emberek_id ORDER BY emberek_nev ASC, esemeny_datuma DESC
‹ MYSQL LIMIT másként PhpMyAdmin vs UTF8 export ›
 ■
1

Sok megoldás

vbence · 2008. Szep. 7. (V), 18.12
Subquery és group concat is lehet jó megoldás...
2

Egy kicsit segíts légyszi!

s_volenszki · 2008. Szep. 7. (V), 18.52
Szia!

Kérlek segíts egy kicsit, kész vagyok az agyamra, egész nap ezt a nyamvadt lekérdezést feszegetem és teljesen belefásultam...

Most döbbentem rá, hogy még azt sem tudom lekérdezni, hogy az események táblában egy-egy ember azonosítóhoz milyen legnagyobb dátum tartozik, mert ha ezt mondom:

SELECT *,MAX(`esemeny_datuma`) FROM `esemenyek` GROUP BY `esemenyek_emberek_id`
akkor hozza a dátumot rendben, de a rekord az az, amelyik amúgy a GROUP-val is visszatérne.

Gondolom a Subquery-ben ennek kellene szerepelnie, mondjuk úgy, hogy az esemeny_id-vel tér vissza és a fő lekérdezés azzal dolgozik:

SELECT * FROM esemenyek, WHERE id = (SELECT id FROM esemenyek WHERE adott id-hez tartozó MAX(esemeny_datuma))
7

order by + limit 1?

Szekeres Gergő · 2008. Szep. 7. (V), 20.15
maxot én nem szoktam használni, csak ha valóban group byolni kell az eredményhalmazt(ahogy értem nálad nem kell). valami ilyesmit írnék:

select * FROM esemenyek ORDER BY id LIMIT 1
8

Miért is?

s_volenszki · 2008. Szep. 7. (V), 20.38
Ha van n darab emberem az emberek táblában és ahhoz tartozik n*m de legalább n*1 esemény az esemenyek táblában, akkor ha jól értelek a

SELECT * FROM esemenyek ORDER BY id DESC LIMIT 1
a legutolsó eseménnyel fog visszatérni, amelyik esemény bármelyik egy darab emberé lehet, nekem viszont az összes ember legutolsó eseményére van szükségem (ha van n emberem és a születésük az első esemény, akkor az én eredményem minimális esetben n darab találattal kell, hogy rendelekzzen).

Egyébként ha a legutolsó eseményre lenne szükségem, én is ehhez hasonló lekérdezéssel kezdenék.

s_volenszki
11

jogos

Szekeres Gergő · 2008. Szep. 8. (H), 10.12
nem olvastam végig az első bejegyzést.. így jártam. :)
3

Valami már lesz!

s_volenszki · 2008. Szep. 7. (V), 18.55
Sokszor az is sokat segít, hogy egyáltalán leírom a problémát...

SELECT MAX(`esemeny_id`),MAX(`esemeny_datuma`) FROM `esemenyek` GROUP BY `esemenyek_emberek_id`
Így most megvan a rekord azonosítója, ahol az ahhoz az emberhez tartozó legutolsó dátum található!

Hajtom tovább! :)

Köszi az ösztönzést!
9

ez biztosan rossz

Hodicska Gergely · 2008. Szep. 8. (H), 02.02
Semmi nem biztosítja neked, hogy a max id és a max datum az ugyanahhoz a rekordhoz fog tartozni.

Szerintem erre van szükséged: http://weblabor.hu/forumok/temak/17200.


Üdv,
Felhő
10

Igen az!

s_volenszki · 2008. Szep. 8. (H), 07.55
Szia!

Igen valóban semmi garancia, sőt a tapasztalat szerint sosem egyezik! Vess egy óvatlan pillantást kérlek a #6 kommentemre!

Üdv,
s_volenszki
4

...

s_volenszki · 2008. Szep. 7. (V), 19.36
Megint beragadtam...

Az biztos, hogy az esemeny_id autoincrement, tehát a következő lekérdezés eredménye:

SELECT MAX(`esmeny_id`) FROM `esemenyek` GROUP BY `esemenyek_emberek_id`;
az összes rekord azonosító, amely nekem kell.

Ha ezt teszem meg subquery-nek:

SELECT * FROM `esemenyek` WHERE esemeny_id = ( SELECT MAX(`esmeny_id`) FROM `esemenyek` GROUP BY `esemenyek_emberek_id`)
akkor hibaüzenetet kapok, hogy a subquery eredménye több mint egy sor ami logikus.
5

Megint lendületet kaptam!

s_volenszki · 2008. Szep. 7. (V), 19.53
Megszületett a teljes lekérdezés subquery-je:

SELECT * FROM `esemenyek` WHERE `esmeny_id` IN (SELECT MAX(`esmenyid`) FROM `esemenyek` GROUP BY `esemenyek_meberek_id`)
Most jöhet a lekérdezés első része!

:)
6

És kész!

s_volenszki · 2008. Szep. 7. (V), 20.04

SELECT * FROM `emberek`
   JOIN `esemenyek` ON esemenyek.esemenyek_emberek_id = emberek.emberek_id
   WHERE `emberek_id` IN (
     SELECT `esemenyek_emberek_id` FROM `esemenyek` WHERE `esemeny_id` IN (
         SELECT MAX(`esemeny_id`) FROM `esemenyek` GROUP BY `esemenyek_emberek_id`
     )
  )
12

...

jaczkog · 2008. Szep. 8. (H), 10.40
Nekem ez egy kicsit szebb megoldásnak tűnik:
select * from `emberek` join
  (select * from
    (select * from `esemenyek` order by esemeny_datuma desc) e
  group by esemenyek_emberek_id) esemenyek
on esemenyek.esemenyek_emberek_id = emberek.emberek_id order by emberek_nev;
15

Nem teljesen értem.

s_volenszki · 2008. Szep. 8. (H), 17.01
Szia!

Nem teljesen értem, valószínüleg az én tudásom hiányos, de mit jelent az az "e" ami a második subquery-t köti össze az eslő subquery group-jával?
23

e

jaczkog · 2008. Szep. 10. (Sze), 11.18
Minden subquery-t el kell nevezni. Írhattam volna kiskutyát is, lényeg hogy kell oda írni valamit.
13

Nem az igazi

tgr · 2008. Szep. 8. (H), 10.59
Hha a legkülső selectben az emberek_id-re szűrsz, akkor biztos nem csak a legutolsó eseményt kapod vissza. Valami ilyesmire gondolhattál:

SELECT * FROM emberek JOIN esemenyek ON esemenyek_emberek_id = emberek_id
WHERE esemeny_id IN (
  SELECT MAX(esemeny_id) FROM esemenyek GROUP BY esemenyek_emberek_id
);
Persze ez is csak akkor működik, ha az egy emberhez tartozó események időrendben lettek felvéve, és a későbbinek nagyobb az id-je. Máskülönben valami ilyesmi lehet jó:

SELECT * FROM emberek JOIN esemenyek ON esemenyek_emberek_id = emberek_id
WHERE (esemenyek_emberek_id, esemeny_datuma) IN (
  SELECT esemenyek_emberek_id, MAX(esemeny_datuma) FROM esemenyek GROUP BY esemenyek_emberek_id
);
16

Igen!

s_volenszki · 2008. Szep. 8. (H), 17.04
Igen, a bejegyzések időrendben történnek, tehát az azonosító ugyan úgy nagyobb mint a dátum, ami a rekordhoz tartozik. Az első lekérdezésed elég könnyen értelmezhető (még számomra is).

Köszi!
14

számított mező

tanar46 · 2008. Szep. 8. (H), 14.49
Én biztos, hogy számított mezővel csinálnám valahogy így:
SELECT
emberek_nev,

(SELECT esemeny_leirasa FROM esemeny 
WHERE esemeny_emberek_id=emberek.emberek_id 
ORDER BY esemeny_datuma ASC LIMIT 1) AS elso_esemeny,

(SELECT esemeny_leirasa FROM esemeny 
WHERE esemeny_emberek_id=emberek.emberek_id 
ORDER BY esemeny_datuma DESC LIMIT 1) AS utolso_esemeny

FROM emberek
Üdv!
17

Hú....

s_volenszki · 2008. Szep. 8. (H), 17.05
Szia!

Ez annyira egyszerű, hogy már fáj! Hihetelen...

Köszi
18

teljesítmény

Hodicska Gergely · 2008. Szep. 8. (H), 17.06
Csak ez sok adat esetén elég lassú lesz a correlated subquery miatt. Továbbra is úgy vélem, hogy a kérdező problémája és ez ( http://weblabor.hu/forumok/temak/17200 ) tök ugyanaz.


Üdv,
Felhő
19

teljesítmény?

tanar46 · 2008. Szep. 8. (H), 19.45
Talán 1 fokkal gyorsabb:
SELECT
emberek.emberek_nev,
esmenyek.esemeny_leirasa
FROM 
emberek
LEFT JOIN esemenyek ON esemenyek.esemenyek_ID=emberek.emberek_ID
WHERE
(esemenyek.esemenyek_id = (SELECT esemenyek_id FROM esemenyek AS e 
		WHERE e.esemenyek_emberek_id=emberek.emberek_id
		ORDER BY esemeny_datuma DESC LIMIT 1
		)
)
Én jobbat nem tudok, de mindjárt jönnek a guruk... :)
21

ez kb. ugyanaz

Hodicska Gergely · 2008. Szep. 8. (H), 23.30
Itt ugyanaúgy az történik, hogy minden egy sorra ki kell értékelni a where feltételben szereplő subquery-t, amitől elég jól be tud lassulni a lekérdezés.


Üdv,
Felhő
20

Egy kicsit megrémültem...

s_volenszki · 2008. Szep. 8. (H), 20.50
Szia!

Nem hagytam figyelmen kívül azt ezt megelőző hozzászólásodban sem az általad javasolt esetet, de bevallom férfiasan, egy kicsit megrémültem tőle. Azt hiszem, a hétvégén egy csendes éjszakán átrágom rajta magam!


Köszönöm,
s_volenszki
22

magyarázat

Hodicska Gergely · 2008. Szep. 9. (K), 10.10
A mysqlben tudsz használni user változókat. Ezek a @-os valamik a queryben. Ezek érvényessége egy sessionre (DB, nincs köze a PHP-s sessionhöz) vonatkozik, ezért ha a queryt egyszer adod ki, akkor nem kell külön nullázni őket.

A query alapvetően két részből áll. Mindkettő magjában ugyanaz a query áll: lekérdezzük a sorokat az adott rendezésnek megfelelően (nálad a dátum) úgy, hogy még a sorok mellé csapunk egy változót, ami szépen egyesével növekedik, és mindkét esetben ugyanahhoz a sorhoz ugyanaz az érték tartozik (mert a rendezés egyezik a két esetben).

Ezt követően az alsó részben elvégezzük a megfelelő csoportosítást (esetedben ember), majd kiválasztjuk a csoportből a számunkra szükséges sort (nálad ez az utolsó esemény, rendezéstől függően max/min rownum2).

Ezután jön a join, ami a csoportosítás alapját képző mező szerint végzünk. Ennek az eredménye, hogy minden egyes sorhoz egy olyan adat fog párosuli, ami azt mutatja, hogy az ő "kategóriájában" mi a kiválasztott rownum2 érték.

Mivel a rownum és a rownum2 szinkronban vannak, ezután már csak azokat a sorokat kell kiszűrnünk, ahol a pl. esetedben a kettő megegyezik.


Üdv,
Felhő
24

Nem egyszerű...

s_volenszki · 2008. Szep. 10. (Sze), 15.47
Szia!

Köszönöm a magyarázatot.

Igazából megcsináltam és tökáletesen működik, de hazudnék ha azt mondanám, teljes egészében értem!

Így néz ki a legkérdezés, olyn nagy különbség nincs az általad gex-nek ajánlott meg ez között:

SET @rownum =0;
SET @rownum2 =0;

SELECT  
    *  
FROM
    (  
        SELECT
            @rownum := @rownum+1 AS rownum,
            esemenyek.*
        FROM
            `esemenyek`
        ORDER BY
            `esemeny_datuma` DESC
    ) sub  
    INNER JOIN  
    (  
        SELECT  
            esemenyek_emberek_id, MAX(rownum) AS legutolso_esemeny_sroszama_minden_emberhez  
        FROM  
            (  
                SELECT
                    @rownum2 := @rownum2+1 AS rownum,
                    esemenyek.*
                FROM
                    `esemenyek`
                ORDER BY
                    `esemeny_datuma` DESC
            ) sub2sub  
        GROUP BY  
            esemenyek_emberek_id  
    ) sub2  
    ON sub.esemenyek_emberek_id = sub2.esemenyek_emberek_id GROUP BY sub.esemenyek_emberek_id
Hajaj...

Tehát az tröténik, hogy az első sub-ban lekérdezem az összes eseményt dátumra csökkenő sorrendben, majd az inner join-nal, mint egy rekurzive, a második sub-ot végigtekeri újra az összes eseményen, de azt már úgy, hogy a sorszámozás után kizárólag a legnagyobb sorszámuval tér vissza.

Ekkor ott tartunk, hogy van egy olyan eredményünk, amiben annyiszor szerepelaz ember, ahány eseménye összesen van, de a JOIN-olt sub2 viselkedése miat, az összes eredménye ugyan úgy a legnagyobb sorszámú.

Ezt az eredményt GROUP-olom emberek_id-re és megvan amit kerestem...

Jaj, nem túl meggyőző...

s_volenszki

szerk:

Most vettem észre, ha kiveszem a GROUP-ot a végéről, akkor látom az összes találatot, és ami a lényeg, hogy a GROUP-olás előtt, olyan sorrendbe rendezi a lekérdezés eredményét, hogy a GROUP tulajdonsága, miszerint az elsővel tér vissza megfelelő ez elvárt adatokhoz képest, ugyanis olyan, mintha minde egyes létrejövő GROUP-nak lenne ORDER-e!
25

ORDER BY

Poetro · 2008. Szep. 10. (Sze), 17.16
Az ORDER BY miért kell a subquery-kbe, ha úgysem raksz rá limit-et? inkább a nagy lekérdezésbe raknám a rendezést, igy nem kell többször rendezni.
26

pont az a lényeg :)

Hodicska Gergely · 2008. Szep. 10. (Sze), 20.51
Olvasd el a magyarázat című hozzászólásom: az azonos rendezés az alapja ennek az egésznek.


Üdv,
Felhő
27

Baj van, segítség!

s_volenszki · 2008. Szep. 11. (Cs), 13.00
Sziasztok!

Elég jól összeált ez a lekérdezés, szinte teljesen értem is, viszont van egy kis gond! Arról volt ugye szó, hogy a user változó
Ezek érvényessége egy sessionre (DB, nincs köze a PHP-s sessionhöz) vonatkozik...

azaz, ha php-ból műveltetem az adatbázist, akkor az egy session-nek a mysql_query("") idézőjel közé írt művelete számit.

Az az én bajom, hogy Ha nem állítom be ezeket a változókat a konkrét lekérdezés elején, akkor NULL értéket kapok a sorszámok helyett mindenhol, épp úgy, ahogyan a lekérdezés eredményéül szolgáló legutolso_esemeny_sroszama_minden_emberhez is NULL lesz!

Próbáltam beletenni a lekérdezésbe vesszővel a SET-et de úgy szintaktikai hibát kapok. Van valakinek valami ötlete?

szerk:

Baromságot írtam, de csak azért gondolom, mert kipróbáltam, hogy a lekérdezés előtt két különálló mysql_query()-be beleraktarm a két user változó SET-jét és működik! Akkor most mi is a DB session? Mikor kezdődik és meddíg érvényes?
28

inicializálás

Hodicska Gergely · 2008. Szep. 11. (Cs), 14.14
A DB session az ott kezdődik, amikor csatlakozol a DB-hez, és addig tart, amíg bezárod a kapcsolatot.

A fenti problémádra elég lenne egy if()-et használni, de szebb, ha minden esetben inicializálod a változókat, amit úgy tehetsz meg, hogy a FROM részhez hozzácsapod ezt: FROM (SELECT @rownum:=0) foo, ... .


Üdv,
Felhő
29

Értem.

s_volenszki · 2008. Szep. 11. (Cs), 15.43
Értem, meg fogom nézni!

Felmerült még egy kérdésem, bonyolódik a helyzet.

Igazából arra gondoltam, hogy majd ha ez a lekérdezés meglesz, akkor tovább jutok, de egyenlőre nem sikerült. Kérek még egy kicsi ösztönzést! :)

Az ezidági tárgyalt példa időközben kiegészült, azaz:

Az emberek táblából lekérdezni az összes embert, úgy, hogy hozzá legyen kötve az épp adott emberrel történt legutolsó esemény. Ez ugye fordult egy kicsit, mert az esmények táblában található távoli kulcs alapján (ami az ember azonosítója), megszerezzük a legutolsó eseményeket és a hozzájuk tartozó ember azonosítót, majd hozzákötöm az emberek táblát ezen a ponton:

SELECT
    *
FROM
    (  
        SELECT
            @rownum := @rownum+1 AS rownum,
            esemenyek.*
        FROM
            `esemenyek`
        JOIN
           `emberek`
        ON
            emberek.emberid = esemenyek.esemenyek_emberek_id
        ORDER BY
            `esmeny_datuma` DESC
    ) sub  
INNER JOIN  
(  
    SELECT  
        esemenyek_emberek_id, MAX(rownum) AS legutolso_esemeny_sroszama_minden_emberhez
    FROM
        (  
            SELECT
                @rownum2 := @rownum2+1 AS rownum,
                esemenyek.*
            FROM
                `esemenyek`
            JOIN
               `emberek`
            ON
                emberek.emberid = esemenyek.esemenyek_emberek_id
            ORDER BY
                `esmeny_datuma` DESC
        ) sub2sub  
    GROUP BY  
        esemenyek_emberek_id  
) sub2  
ON
    sub.esemenyek_emberek_id = sub2.esemenyek_emberek_id
GROUP BY
    sub.esemenyek_emberek_id
és a teljes végerdményhez hozzá kellen kapcsolnom mégegy táblát, amiben azok a felhasznalók szerepelnek, akik az embereket rögzítették. Természetesen a felhasznaló azonosítója része az emberek táblának, mezö szerint emberek.user_id!

Megpróbáltam a lekérdezés végén JOIN-olni, de azt az erdményt kapom mindíg, hogy
Unknown table 'felhasznalok' in on clause

SELECT
    *
FROM
    (  
        SELECT
            @rownum := @rownum+1 AS rownum,
            esemenyek.*
        FROM
            `esemenyek`
        JOIN
           `emberek`
        ON
            emberek.emberid = esemenyek.esemenyek_emberek_id
        ORDER BY
            `esmeny_datuma` DESC
    ) sub  
INNER JOIN  
(  
    SELECT  
        esemenyek_emberek_id, MAX(rownum) AS legutolso_esemeny_sroszama_minden_emberhez
    FROM
        (  
            SELECT
                @rownum2 := @rownum2+1 AS rownum,
                esemenyek.*
            FROM
                `esemenyek`
            JOIN
               `emberek`
            ON
                emberek.emberid = esemenyek.esemenyek_emberek_id
            ORDER BY
                `esmeny_datuma` DESC
        ) sub2sub  
    GROUP BY  
        esemenyek_emberek_id  
) sub2  
ON
    sub.esemenyek_emberek_id = sub2.esemenyek_emberek_id
JOIN
    felhasznalok
ON
    felhasznalok.felhasznaloid = emberek.user_id
GROUP BY
    sub.esemenyek_emberek_id
Nagyjábol értem, hogy miért, valószínüleg a JOIN-t ahol alkalmazni akarom, ott még tart az INNER JOIN fuggőségének elemzése.

Már szinte minden variációt kipróbáltam. Betettem a sub-okba is, nem jártam eredménnyel.

Ha logikusan gondolkozom, csak akkor kötöm hozzá a felhasználó táblát, ha már minden végleges adat megvan. Már arra is gondoltam, hogy elkezdem az egész lekérdezést azzal, hogy kiválasztok minden felhasználót a felhasználük táblából, hozzáközöm az egész jelenlegi lekérdezést és a végén az ON kitételben azt mondom, ahol felhasznalo.felhasznaloid = emberek.user_id.

Kérek véleményt mielőtt padlóra küldöm az sql szervert!

s_volenszki