trebuie să apară în clauza GROUP BY sau pot fi utilizate într-o funcție agregată

Da, aceasta este o comună de agregare problema. Înainte de SQL3 (1999), câmpurile selectate trebuie să apară în anii `GROUP BY clauza de[*].

Pentru a soluționa această problemă, trebuie să calculeze agregate într-o sub-interogare și apoi se alăture cu el însuși pentru a obține suplimentare de coloane pe care le'd trebuie sa arate:

SELECT m.cname, m.wmname, t.mx
FROM (
    SELECT cname, MAX(avg) AS mx
    FROM makerar
    GROUP BY cname
    ) t JOIN makerar m ON m.cname = t.cname AND t.mx = m.avg
;

 cname  | wmname |          mx           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | usopp  |     5.0000000000000000

Dar puteți folosi, de asemenea, fereastra de funcții, care pare mai simplu:

SELECT cname, wmname, MAX(avg) OVER (PARTITION BY cname) AS mx
FROM makerar
;

Singurul lucru cu această metodă este că acesta va afișa toate înregistrările (fereastra de funcții nu de grup). Dar se va arăta corect (adică maxed la cname nivel a) "MAX." pentru țară, în fiecare rând, astfel încât acesta's până la tine:

 cname  | wmname |          mx           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | luffy  |     5.0000000000000000
 spain  | usopp  |     5.0000000000000000

Soluția, fără îndoială, mai puțin elegant, pentru a arăta doar (cname, wmname) tupluri potrivire valoarea maximă, este:

SELECT DISTINCT /* distinct here matters, because maybe there are various tuples for the same max value */
    m.cname, m.wmname, t.avg AS mx
FROM (
    SELECT cname, wmname, avg, ROW_NUMBER() OVER (PARTITION BY avg DESC) AS rn 
    FROM makerar
) t JOIN makerar m ON m.cname = t.cname AND m.wmname = t.wmname AND t.rn = 1
;

 cname  | wmname |          mx           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | usopp  |     5.0000000000000000

[*]: Destul de interesant, chiar dacă spec fel de vă permite să selectați non-grupate pe domenii, principalele motoare par să nu place. Oracle și SQLServer doar don't permite acest lucru la toate. Mysql folosit pentru a permite în mod implicit, dar acum, de când 5.7 administratorul trebuie să activați această opțiune (ONLY_FULL_GROUP_BY) manual în configurarea serverului pentru ca această caracteristică să fie acceptată...

În Postgres, puteți utiliza, de asemenea, speciale DISTINCT PE (expresia) sintaxa:

SELECT DISTINCT ON (cname) 
    cname, wmname, avg
FROM 
    makerar 
ORDER BY 
    cname, avg DESC ;

Problema cu specificarea non-grupate și non-agregat domenii în grupa de` selectează este ca motorul nu are nici o modalitate de a ști care înregistrează's teren ar trebui să se întoarcă în acest caz. Este prima? Este ultima? Există, de obicei, nici o înregistrare care în mod natural corespunde rezultat agregat ("min" și "max." sunt și excepții).

Cu toate acestea, există o soluție: asigurați-câmp obligatoriu agregate la fel de bine. În posgres, acest lucru ar trebui să funcționeze:

SELECT cname, (array_agg(wmname ORDER BY avg DESC))[1], MAX(avg)
FROM makerar GROUP BY cname;

Rețineți că acest lucru creează o matrice de toate wnames, comandat de către avg, și returnează primul element (tablouri în postgres sunt 1-based).

SELECT t1.cname, t1.wmname, t2.max
FROM makerar t1 JOIN (
    SELECT cname, MAX(avg) max
    FROM makerar
    GROUP BY cname ) t2
ON t1.cname = t2.cname AND t1.avg = t2.max;

Folosind rang() fereastra function:

SELECT cname, wmname, avg
FROM (
    SELECT cname, wmname, avg, rank() 
    OVER (PARTITION BY cname ORDER BY avg DESC)
    FROM makerar) t
WHERE rank = 1;

Notă

Unul se va păstra mai multe valori max per grup. Dacă vrei numai o singură înregistrare pentru fiecare grup, chiar dacă există mai mult de o înregistrare cu avg egală cu max ar trebui să verificați @ypercube's a răspunde.

Pentru mine, este vorba despre un "frecvente agregare problema", dar doar despre un incorectă de interogare SQL. Singur răspuns corect pentru "selectați maxim avg pentru fiecare cname..." este

SELECT cname, MAX(avg) FROM makerar GROUP BY cname;

Rezultatul va fi:

 cname  |      MAX(avg)
--------+---------------------
 canada | 2.0000000000000000
 spain  | 5.0000000000000000

Acest rezultat, în general, răspunsurile la întrebarea "ceea Ce este cel mai bun rezultat pentru fiecare grup?". Vom vedea că cel mai bun rezultat pentru spania este 5 și pentru canada cel mai bun rezultat este de 2. Este adevărat, și nu există nici o eroare. Dacă avem nevoie pentru a afișa wmname de asemenea, trebuie să se răspundă la întrebarea: "Ce este REGULI pentru a alege wmname din care rezultă set?" Sa's a modifica datele de intrare un pic pentru a clarifica o greseala:

  cname | wmname |        avg           
--------+--------+-----------------------
 spain  | zoro   |  1.0000000000000000
 spain  | luffy  |  5.0000000000000000
 spain  | usopp  |  5.0000000000000000

Ce rezultat te aștepți pe runnig această interogare: SELECTAȚI cname, wmname, MAX(avg) DIN makerar GRUP DE cname;? Ar trebui să fie spania+luffy " sau " spania+usopp? De ce? Nu este determinat în interogare cum de a alege "bine" wmname dacă mai multe sunt potrivite, astfel încât rezultatul este, de asemenea, determinată. Ca's de ce SQL interpret returnează o eroare - interogarea nu este corect.

În alte cuvinte, nu există nici un răspuns corect la întrebarea "Care este cel mai bun din "spania" grup?". Luffy nu este mai bun decât usopp, pentru că usopp are aceeași "scor".

Recent am rula în această problemă, atunci când încearcă să numere folosind cazul, și a constatat că schimbarea ordinii de "care" și "numărul" declarații rezolvă problema:

SELECT date(dateday) as pick_day,
COUNT(CASE WHEN (apples = 'TRUE' OR oranges 'TRUE') THEN fruit END)  AS fruit_counter

FROM pickings

GROUP BY 1

În loc de a folosi - în cele din urmă, de unde am luat erorile care mere și portocale ar trebui să apară în funcții agregate

CASE WHEN ((apples = 'TRUE' OR oranges 'TRUE') THEN COUNT(*) END) END AS fruit_counter

Acest lucru pare să funcționeze la fel de bine

SELECT *
FROM makerar m1
WHERE m1.avg = (SELECT MAX(avg)
                FROM makerar m2
                WHERE m1.cname = m2.cname
               )