harus muncul dalam klausa GROUP BY atau yang akan digunakan dalam fungsi agregat

Ya, ini adalah umum agregasi masalah. Sebelum SQL3 (1999), bidang yang dipilih harus muncul dalam GROUP BY klausul[*].

Untuk solusi masalah ini, anda harus menghitung agregat dalam sub-query dan kemudian bergabung dengan dirinya sendiri untuk mendapatkan tambahan kolom anda'd butuhkan untuk menunjukkan:

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

Tapi anda juga dapat menggunakan fungsi jendela, yang terlihat lebih sederhana:

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

Satu-satunya hal dengan metode ini adalah bahwa hal itu akan menunjukkan semua catatan (window fungsi tidak kelompok). Tapi itu akan menunjukkan yang benar (yaitu maxed di cname level) MAX untuk negara di setiap baris, sehingga's up untuk anda:

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

Solusinya, bisa dibilang kurang elegan, untuk menunjukkan satu-satunya (cname, wmname) tupel pencocokan nilai max, adalah:

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

[*]: Cukup menarik, meskipun spec semacam ini memungkinkan untuk memilih non-dikelompokkan bidang, mesin utama tampaknya tidak benar-benar seperti itu. Oracle dan SQLServer hanya don't membiarkan ini semua. Mysql digunakan untuk memungkinkan secara default, tapi sekarang sejak 5.7 administrator harus mengaktifkan opsi ini (ONLY_FULL_GROUP_BY) secara manual pada konfigurasi server untuk fitur ini harus didukung...

Di Postgres, anda juga dapat menggunakan khusus yang BERBEDA PADA (ekspresi) sintaks:

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

Masalah dengan menentukan non-dikelompokkan dan non-agregat di bidang group by memilih adalah bahwa mesin tidak memiliki cara untuk mengetahui yang merekam's bidang itu harus kembali dalam kasus ini. Itu dulu? Itu tadi? Biasanya ada tidak ada catatan yang secara alami sesuai dengan agregat hasil (min dan max adalah pengecualian).

Namun, ada solusi: membuat bidang yang diperlukan dikumpulkan juga. Di posgres, ini harus bekerja:

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

Perhatikan bahwa hal ini menciptakan sebuah array dari semua wnames, diperintahkan oleh avg, dan kembali elemen pertama (array dalam postgres adalah 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;

Menggunakan rank() fungsi jendela:

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

Catatan

Salah satu akan mempertahankan beberapa nilai max per kelompok. Jika anda ingin hanya satu record per kelompok bahkan jika ada lebih dari satu record dengan avg sama dengan max anda harus memeriksa @ypercube's jawaban.

Bagi saya, ini bukan tentang "umum agregasi masalah", tapi hanya mengenai salah query SQL. Satu jawaban yang benar untuk "pilih maksimum avg untuk setiap cname..."

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

Hasilnya akan menjadi:

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

Hasil ini secara umum jawaban pertanyaan "Apa hasil yang terbaik bagi masing-masing group?". Kita melihat bahwa hasil yang terbaik untuk spanyol adalah 5 dan kanada yang terbaik hasilnya adalah 2. Itu adalah benar, dan tidak ada kesalahan. Jika kita perlu untuk menampilkan wmname juga, kita harus menjawab pertanyaan: "Apa PERATURAN untuk memilih wmname dihasilkan dari set?" Let's mengubah data masukan sedikit untuk memperjelas kesalahan:

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

Yang hasil yang anda harapkan pada runnig query ini: PILIH cname, wmname, MAX(avg) DARI makerar KELOMPOK DENGAN cname;? Harus itu spanyol+luffy atau spanyol+usopp? Mengapa? Hal ini tidak bertekad dalam query bagaimana memilih "baik" wmname jika beberapa yang cocok, sehingga hasilnya juga tidak ditentukan. Yang's mengapa SQL juru kembali kesalahan - query adalah tidak benar.

Dalam kata lain, tidak ada jawaban yang benar untuk pertanyaan "siapa Yang terbaik di spanyol kelompok?". Luffy tidak lebih baik dari usopp, karena usopp sama "nilai".

Baru-baru ini saya mengalami masalah ini, ketika mencoba untuk menghitung menggunakan kasus, dan menemukan bahwa mengubah urutan yang dan menghitung laporan perbaikan masalah:

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

Alih-alih menggunakan - in yang terakhir, di mana saya mendapat kesalahan apel dan jeruk harus muncul dalam fungsi agregat

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

Hal ini tampaknya bekerja dengan baik

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