|
SQL baze podataka - Sponzor: Baze-Podataka.net MySQL, MSSQL, Oracle, Access, ODBC. Ako imate problem brže i preciznije ćete dobiti odgovor ako priložite strukturu tabela ili skript koji kreira tabele i puni ih test podacima umesto što to problem opisujete samo rečima. Sponzor: Baze-Podataka.net - Blog o bazama podataka |
|
Alati teme | Način prikaza |
24. 05. 2014. | #1 |
Ivan Dilber
Sir Write-a-Lot
|
Kako dohvatiti count() iz subquery-ja?
Trazim duplicate records, ali mi treba da znam i koliko duplikata ima za svaki (plus bi bilo dobro da po tome uradim ORDER BY).
Za sad imam ovakav upit koji nalazi sve usere koji imaju isti payment email: Kôd:
SELECT u.`id`, u.`username`, u.`email`, u.`first_name`, u.`last_name`, mb.`moneybookers_email` FROM users u JOIN `payment` mb ON u.id=mb.`user_id` WHERE mb.`moneybookers_email` IN ( SELECT `moneybookers_email` FROM `payment` GROUP BY `moneybookers_email` HAVING count(*)>1 ) ORDER BY mb.`moneybookers_email`
__________________
Leadership is the art of getting people to want to do what you know must be done. |
24. 05. 2014. | #2 | |
Aleksandar Janković
Qualified
Datum učlanjenja: 16.10.2010
Lokacija: Bg-Sd
Poruke: 165
Hvala: 70
54 "Hvala" u 36 poruka
|
Nije testirano ali čisto da ti ukažem na drugačiji pristup pa ti napravi izmene kako tebi odgovara:
Citat:
__________________
ajankovic.com] |
|
24. 05. 2014. | #3 |
Ivan Dilber
Sir Write-a-Lot
|
Da, tako nesto sam i ja skuckao po preporuci ljudi da koristim JOIN umesto IN...
Ispalo je ovako, ako nekom zatreba: Kôd:
SELECT u.`id`, u.`username`, u.`email`, u.`first_name`, u.`last_name`, sub.`moneybookers_email`, sub.cnt FROM users u JOIN `payment` mb ON u.id=mb.`user_id` JOIN (SELECT count(*) AS cnt, `moneybookers_email` FROM `payment` GROUP BY `moneybookers_email` HAVING cnt>1 ) sub ON mb.`moneybookers_email`=sub.`moneybookers_email` ORDER BY sub.cnt DESC, mb.`moneybookers_email`
__________________
Leadership is the art of getting people to want to do what you know must be done. |
"Hvala" ivanhoe za poruku: |
26. 05. 2014. | #4 |
expert
Grand Master
|
WHERE ... IN (
SELECT radi fino kad nemas puno record-a. Mysql pocinje da se muci ako brojka dostigne vise nula. Oracle, pak, ima limit, koliko je moguce recorda staviti u IN() - nesta oko 1000 ili 10000 Tako da je JOIN i zbog toga preporucljiviji. |
|
|