DevProTalk - Pogledajte određenu poruku - Kako dohvatiti count() iz subquery-ja?

ivanhoe · 24. 05. 2014.

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`

24. 05. 2014.	#3
ivanhoe Ivan Dilber Sir Write-a-Lot Datum učlanjenja: 18.10.2005 Lokacija: Bgd Poruke: 5.320 Hvala: 104 2.344 "Hvala" u 583 poruka	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.