Dapatkan rekod nilai maksimum dalam setiap hasil SQL berkumpulan

Question

Bagaimana untuk mendapatkan baris yang mengandungi nilai maksimum untuk setiap kumpulan?

Saya melihat beberapa variasi yang terlalu rumit, tetapi tidak ada yang memberikan jawapan yang baik. Saya cuba contoh paling mudah:

Memandangkan jadual di bawah, yang mengandungi lajur untuk orang, kumpulan dan umur, bagaimana anda mendapatkan orang tertua dalam setiap kumpulan? (Tindakan seri dalam kumpulan harus memberikan hasil pertama dalam susunan abjad)

Orang |
---
Bob |. 32
Jill |. 34
Shawn|. 1 |
Jake |. 29
Paul |. 36
Laura|. 39
Set hasil yang diingini: 
Shawn |
Laura |. 39

Answer 1

Penyelesaian yang betul ialah:

SELECT o.*
FROM `Persons` o                    # 'o' from 'oldest person in group'
  LEFT JOIN `Persons` b             # 'b' from 'bigger age'
      ON o.Group = b.Group AND o.Age < b.Age
WHERE b.Age is NULL                 # bigger age not found

Cara ia berfungsi:

Ia akan sepadan dengan satu atau lebih baris dalam o中的每一行与具有相同Group列值和较大Age列值的b中的所有行进行匹配。任何o中的行如果在Age列中没有其组中的最大值，则会与b.

LEFT JOIN使其将组中的最年长的人（包括那些独自一人的人）与来自b的一行NULLBuat perlawanan ('Tiada sesiapa yang lebih tua dalam kumpulan').
Menggunakan INNER JOIN akan menyebabkan garisan ini tidak sepadan dan ia akan diabaikan.

WHERE子句仅保留从b中提取的字段中具有NULL OK. Mereka adalah yang paling tua dalam setiap kumpulan.

Bacaan lanjut

Penyelesaian ini dan banyak lagi diterangkan secara terperinci dalam buku SQL Antipatterns Jilid 1: Mengelakkan Perangkap Pengaturcaraan Pangkalan Data.

Answer 2

Terdapat cara yang sangat mudah untuk melakukan ini dalam mysql:

select * 
from (select * from mytable order by `Group`, age desc, Person) x
group by `Group`

Kaedah ini berfungsi kerana dalam mysql, anda boleh bukan mengagregat bukan kumpulan mengikut lajur, dalam kes ini mysql hanya mengembalikan baris pertama. Penyelesaiannya adalah untuk mengisih data terlebih dahulu dalam susunan yang anda mahu dan kemudian kumpulan mengikut lajur yang anda mahu.

Anda mengelakkan masalah subkueri kompleks yang cuba mencari max() dsb., dan juga mengelakkan masalah mengembalikan berbilang baris apabila terdapat berbilang baris dengan nilai maksimum yang sama (jawapan lain melakukan ini).

Nota: Ini adalah penyelesaian hanya untuk mysql. Semua pangkalan data lain yang saya tahu akan membuang ralat sintaks SQL dengan mesej ralat "Lajur bukan agregat tidak disenaraikan dalam kumpulan mengikut klausa" atau sesuatu yang serupa. Oleh kerana penyelesaian ini menggunakan tidak berdokumen gelagat, orang yang lebih berhemat mungkin mahu memasukkan ujian untuk memastikan ia masih berfungsi jika versi MySQL akan datang mengubah tingkah laku ini.

Kemas kini versi 5.7:

Mulai versi 5.7, sql-mode设置默认包含ONLY_FULL_GROUP_BY, jadi untuk ini berfungsi, anda mesti tidak menggunakan pilihan ini (edit fail pilihan pelayan untuk mengalih keluar tetapan ini).