31. oktober 2004 - 23:30Der er
7 kommentarer og 1 løsning
GROUP BY problem
Jeg har denne SQL sætning: ----------------------------------------------- SELECT nc.country_id,nc.country,nc.country_code, count(nu.[user_id]) as UserCount,count(nl.layout_id) as LayoutCount, count(nlw.language_words_fk_country) as WordCount FROM news_country nc LEFT OUTER JOIN news_user nu ON nu.user_country_id = nc.country_id LEFT OUTER JOIN news_layout nl ON nl.layout_fk_country_id = nc.country_id LEFT OUTER JOIN news_language_words nlw ON nlw.language_words_fk_country = nc.country_id GROUP BY nc.country_id,nc.country,nc.country_code ORDER BY country -------------------------------------------------------
Problemet er at all Counts giver det samme resultat. Altså kan den ikke håndtere mere end én count....
Jeg vil helst ikke gå ind i at lave en Temp tabel med Cursor. Der må være noget jeg har overset...
Det forstår jeg ikke helt. Når jeg nu altid joiner til tabellen "news_country" burde den vel tage det som udgangspunkt i alle 3 joins. Og derved give forskelligt count al efter joinet på "news_country.country_id"
SELECT * FROM (SELECT nc.country_id,nc.country,nc.country_code, COUNT(nu.user_id) AS UserCount FROM news_country nc LEFT OUTER JOIN news_user nu ON nu.user_country_id = nc.country_id GROUP BY nc.country_id,nc.country,nc.country_code) AS count1, (SELECT nc.country_id,nc.country,nc.country_code, COUNT(nl.layout_id) AS LayoutCount FROM news_country nc LEFT OUTER JOIN news_layout nl ON nl.layout_fk_country_id = nc.country_id GROUP BY nc.country_id,nc.country,nc.country_code) AS count2, (SELECT nc.country_id,nc.country,nc.country_code, COUNT(nlw.language_words_fk_country) AS WordCount FROM news_country nc LEFT OUTER JOIN news_language_words nlw ON nlw.language_words_fk_country = nc.country_id GROUP BY nc.country_id,nc.country,nc.country_code) AS count3 WHERE count1.country_id=count2.country_id AND count2.country_id=count3_country_id
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
