reiniger Posted April 16, 2010 Report Share Posted April 16, 2010 Sveiki. Man nepieciešams izveidot reitingu par nobalsotiem rakstiem. Tabulas story id | text story_votes id | id_p | ip | date Idejas smēlos no šejienes mans cods: SELECT story.id, votes FROM story ,(SELECT id_p,COUNT(*) AS votes FROM story_votes GROUP BY id_p) AS votes WHERE (story.id=votes.id_p) ORDER BY `votes`.`votes` DESC Viss izdrukājas skaisti, bet ir viena problēma, ja zem tabulas "story_votes" nav nobalsots nevienu reizi, tad šis stāsts netiek izdrukāts. Ka izdrukāt story bez ievietota record iekš story_votes? P.S: Zinu, ka varu, teiksim ievietot pa vienam ierakstam iekš story_votes un tad pie izdrukāšanas katram rezultātem -1 vai arī piefiksēt visus id un izdrukat story kas nava šajā masīvā, bet gribētos uzzināt ka to ar SQL var dabūt gatavu! Quote Link to comment Share on other sites More sharing options...
Aleksejs Posted April 16, 2010 Report Share Posted April 16, 2010 Vai šitāds nedod vēlamo rezultātu? SELECT story.id AS story_id, COUNT(votes.id_p) AS skaits FROM story LEFT JOIN votes ON story.id = votes.id_p GROUP BY story.id Quote Link to comment Share on other sites More sharing options...
reiniger Posted April 16, 2010 Author Report Share Posted April 16, 2010 (edited) Vai šitāds nedod vēlamo rezultātu? SELECT story.id AS story_id, COUNT(votes.id_p) AS skaits FROM story LEFT JOIN votes ON story.id = votes.id_p GROUP BY story.id Es jau pats no sākuma mēģināju šo variantu, bet mana datubāze kaut ko nežēlīgi domā un nedod man rezultātu. Vienkārši apnīk gaidīt. Story - 375 ieraksti story_votes - 254 730 ieraksti Izpildijas :) Parādu rindas 0 - 29 (378 kopā, Vaicājums ilga 281.3598 s) taka biku pa ilgu... Edited April 16, 2010 by reiniger Quote Link to comment Share on other sites More sharing options...
Aleksejs Posted April 16, 2010 Report Share Posted April 16, 2010 Parādi ko atgriež: EXPLAIN SELECT story.id AS story_id, COUNT(votes.id_p) AS skaits FROM story LEFT JOIN votes ON story.id = votes.id_p GROUP BY story.id un SHOW CREATE TABLE story SHOW CREATE TABLE votes Nav indeksa uz id_p iespējams... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.