MySQL SELECT dengan GROUP BY satu medan dan ORDER BY medan lain

Question

Edit 3 - Versi MySQL ialah 8.0.33.

Edit 2 - Lihat kod kerja akhir di bahagian bawah. Terima kasih @Akina!

Saya mempunyai lembaran markah untuk acara sukan. Jadual mempunyai tiga medan berkaitan yang saya ingin pilih -

1. scoreID sebagai nilai kunci utama
2. classifierID Peta ke kunci utama jadual lain yang mengandungi butiran tentang susun atur kursus tertentu
3. calculatedPercent adalah hasil daripada acara tertentu

Jadual mempunyai tiga medan lain yang saya gunakan dalam klausa WHERE, tetapi ini adalah sampingan.

Saya perlu menjana pertanyaan yang memilih empat nilai terbaik untuk calculatedPercent 选择四个最佳值，并规定 classifierID 不能重复。我需要能够捕获 scoreID dan menyatakan bahawa

tidak boleh diulang. Saya perlu dapat menangkap

untuk digunakan pada peringkat seterusnya proses.

Ini adalah pertanyaan pertama saya: calculatedPercent 值的行选择了 scoreID 值。然后我注意到有几个成员在同一门课程上获得了第一和第二高分，这违反了 classifierID

SELECT `masterScores`.`scoreID`, `masterScores`.`classifierID`, `masterScores`.`calculatedPercent` 
FROM `masterScores` 
WHERE `masterScores`.`memberID` = 3516 AND `masterScores`.`eventDivision` = "O" AND `masterScores`.`scoreUnusable` != "TRUE" 
ORDER BY `masterScores`.`calculatedPercent` DESC LIMIT 4

Pada mulanya saya fikir ini bagus kerana ia memilih baris dengan nilai

tertinggi untuk ahli tertentu. Kemudian saya perhatikan bahawa beberapa ahli mempunyai markah tertinggi pertama dan kedua dalam kursus yang sama, yang melanggar syarat

tiada pertindihan nilai.

Saya mencuba SELECT DISTINCT tetapi akhirnya menyedari apa yang saya perlukan adalah GROUP BY, jadi saya melakukan beberapa penyelidikan dan mendapati bahawa saya mendapat ralat yang berkaitan dengan only_full_group_by apabila melaksanakan pertanyaan dalam MySql, tetapi itu tidak menyelesaikan masalah saya sepenuhnya.

Apa yang saya cuba seterusnya:

SELECT `masterScores`.`scoreID`, `masterScores`.`classifierID`, MAX(`masterScores`.`calculatedPercent`) AS bestPercent 
FROM `masterScores` 
WHERE `masterScores`.`memberID` = 3516 AND `masterScores`.`eventDivision` = "O" AND `masterScores`.`scoreUnusable` != "TRUE" 
GROUP BY `masterScores`.`classifierID` 
ORDER BY bestPercent DESC LIMIT 4

Ini ialah mesej ralat berikut:

#1055 - Ungkapan #1 klausa ORDER BY tiada dalam klausa GROUP BY dan mengandungi lajur bukan agregat ".masterScores.calculatedPercent", yang tidak bergantung pada lajur dalam klausa GROUP BY ini tidak konsisten dengan sql_mode=only_full_group_by tidak serasimasterScores.scoreID 列使用 MIN 和 MAX，但它与预期不符； scoreID 主键值并不总是所选 calculatedPercent 的值。我在某处读到，因为 scoreID

Saya mempertimbangkan untuk menggunakan MIN dan MAX untuk masterScores.scoreID lajur, tetapi ia tidak berfungsi seperti yang dijangkakan

nilai kunci utama tidak selalunya nilai

yang dipilih. Saya membaca di suatu tempat bahawa memandangkan

ialah kunci utama, saya boleh membetulkannya dengan menggunakan pengagregatan ANY_VALUE. Saya mencuba ini:

SELECT ANY_VALUE(`masterScores`.`scoreID`), `masterScores`.`classifierID`, MAX(`masterScores`.`calculatedPercent`) AS bestPercent 
FROM `masterScores` 
WHERE `masterScores`.`memberID` = 3516 AND `masterScores`.`eventDivision` = "O" AND `masterScores`.`scoreUnusable` != "TRUE" 
GROUP BY `masterScores`.`classifierID` 
ORDER BY bestPercent DESC LIMIT 4

1. Pada pandangan pertama, ini nampaknya berkesan, tetapi ia tidak selalu mengembalikan nilai

   yang sepadan dengan nilai Peratus terbaik. classifierID 选择 1 个 calculatedPercent 和 1 个 scoreID 值。如果不按 classifierID 分组，则每个 classifierID

   Sekali lagi, matlamatnya ialah:

2. classifierID 所选的 calculatedPercentMemilih hanya 1

   dan 1
nilai untuk setiap
3. berdasarkan klausa WHERE yang dinyatakan. Tanpa mengumpulkan mengikut

   , setiap calculatedPercent boleh mempunyai antara 0 hingga 400 baris yang memenuhi klausa WHERE, jadi saya rasa GROUP BY sesuai di sini.

4. Pastikan calculatedPercent yang dipilih untuk setiap kumpulan

   ialah nilai tertinggi antara semua pilihan🎜 🎜 🎜🎜Pastikan hanya 4 baris dipilih dan ini adalah baris dengan nilai 🎜 tertinggi dipilih. 🎜 🎜 🎜🎜Pastikan 4 baris yang dipilih diisih mengikut tertib menurun berdasarkan nilai 🎜. 🎜

5. Memastikan scoreID 值实际上代表与选定的 calculatedPercent baris yang sama untuk setiap baris yang dipilih (pada masa ini, ini ialah titik di mana peratusan dikira dan pertanyaan saya gagal).

Berikut ialah subset data, contohnya:

ID Skor	ID Pengelas	Peratusan Terbaik
58007	42	66.60
63882	42	64.69
64685	54	64.31
58533	32	63.20
55867	42	62.28
66649	7	56.79
55392	12	50.28
58226	1	49.52
55349	7	41.10

Ini adalah output yang diingini apabila saya menjalankan pertanyaan:

ID Skor	ID Pengelas	Peratusan Terbaik
58007	42	66.60
64685	54	64.31
58533	32	63.20
66649	7	56.79

Ini adalah output sebenar apabila saya menjalankan pertanyaan:

ID Skor	ID Pengelas	Peratusan Terbaik
55867	42	66.60
64685	54	64.31
58533	32	63.20
55349	7	56.79

Seperti yang ditunjukkan dalam rajah, nilai scoreID baris pertama dan keempat output sebenar adalah tidak betul.

Buat masa ini, saya mengalu-alukan sebarang cadangan.

Edit 2 - Penyelesaian kerja muktamad

WITH cte AS (
    SELECT scoreID, classifierID, calculatedPercent AS bestPercent,
           ROW_NUMBER() OVER (PARTITION BY classifierID ORDER BY calculatedPercent DESC, scoreID DESC) AS rn
    FROM masterScores WHERE memberID = 3516 AND eventDivision = "O" AND scoreUnusable != "TRUE"
)
SELECT scoreID, classifierID, bestPercent
FROM cte
WHERE rn = 1
ORDER BY bestPercent DESC
LIMIT 4

Saya dapat menguji ini terhadap enam kes di mana masalah itu berlaku, dan penyelesaiannya membetulkan setiap isu. Terima kasih sekali lagi @Akina!

Akan menandakan isu ini sebagai selesai.

Answer 1

SELECT t1.scoreID, classifierID, calculatedPercent AS bestPercent 
FROM masterScores t1
NATURAL JOIN (
    SELECT classifierID, MAX(calculatedPercent) AS calculatedPercent
    FROM masterScores t2
    WHERE memberID = 3516 AND eventDivision = "O" AND scoreUnusable != "TRUE" 
    GROUP BY 1
    ORDER BY calculatedPercent DESC LIMIT 4
    ) t2

Jika (classifierID,calculatedPercent) 不唯一，那么每个classifierID anda mungkin menerima berbilang talian. Dalam kes ini anda perlukan

SELECT MAX(t1.scoreID) AS scoreID, classifierID, calculatedPercent AS bestPercent 
FROM masterScores t1
NATURAL JOIN (
    SELECT classifierID, MAX(calculatedPercent) AS calculatedPercent
    FROM masterScores t2
    WHERE memberID = 3516 AND eventDivision = "O" AND scoreUnusable != "TRUE" 
    GROUP BY 1
    ORDER BY calculatedPercent DESC LIMIT 4
    ) t2
GROUP BY 2, 3

PS. Jika versi MySQL anda ialah 8+, anda mesti menggunakan ROW_NUMBER() dalam CTE dan bukannya subkueri.

---

WITH cte AS (
    SELECT scoreID, classifierID, calculatedPercent AS bestPercent,
           ROW_NUMBER() OVER (PARTITION BY classifierID ORDER BY calculatedPercent DESC, scoreID DESC) AS rn
    FROM masterScores 
)
SELECT scoreID, classifierID, bestPercent
FROM cte
WHERE rn = 1