Contoh analisis kedudukan pengelasan dan pengelompokan TOP N dalam MySQL

Struktur jadual

Jadual pelajar adalah seperti berikut:

CREATE TABLE `t_student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `t_id` int DEFAULT NULL COMMENT '学科id',
  `score` int DEFAULT NULL COMMENT '分数',
  PRIMARY KEY (`id`)
);

Data adalah seperti berikut:

Soalan 1: Dapatkan kedudukan lima skor teratas dalam setiap subjek (mengikat dibenarkan)

situasi tie-ing dibenarkan. Keputusan tempat ke-4 dan ke-5, yang akan menghasilkan 5 keping data diperolehi dari 4 teratas. 5 teratas juga adalah 5 keping data.

SELECT
	s1.* 
FROM
	student s1
	LEFT JOIN student s2 ON s1.t_id = s2.t_id 
	AND s1.score < s2.score 
GROUP BY
	s1.id
HAVING
	COUNT( s2.id ) < 5 
ORDER BY
	s1.t_id,
	s1.score DESC

ps: Apabila mengambil 4 tempat teratas

Analisis:

1. Sendiri left Outer join mendapat semua set yang nilai kirinya kurang daripada nilai kanan. Mengambil t_id=1 sebagai contoh, 24 mempunyai 5 markah lebih besar daripadanya (74, 64, 54, 44, 34), kedudukan ke-6, 34 hanya mempunyai 4 markah lebih besar daripadanya, kedudukan ke-5... .74 Tiada siapa lebih besar daripadanya dan dia yang pertama.

SELECT
	* 
FROM
	student s1
	LEFT JOIN student s2 ON s1.t_id = s2.t_id 
	AND s1.score < s2.score

2. Tukarkan peraturan ringkasan kepada SQL untuk menyatakannya, iaitu kumpulan mengikut id (s1.id) setiap pelajar berada di bawah id ini. Nilai lebih besar daripada dia (s2.id)

SELECT
	s1.* 
FROM
	student s1
	LEFT JOIN student s2 ON s1.t_id = s2.t_id 
	AND s1.score < s2.score 
GROUP BY
	s1.id
HAVING
	COUNT( s2.id ) < 5

3. Akhir sekali, klasifikasikan mengikut t_id dan susun mengikut skor dalam susunan terbalik.

Soalan 2: Dapatkan purata markah dua pelajar terakhir dalam setiap mata pelajaran

Dapatkan dua markah terakhir

SELECT
	s1.* 
FROM
	student s1
	LEFT JOIN student s2 ON s1.t_id = s2.t_id 
	AND s1.score > s2.score 
GROUP BY
	s1.id 
HAVING
	COUNT( s1.id )< 2 
ORDER BY
	s1.t_id,
	s1.score

Jika mereka wujud bersebelahan, keputusan yang sama mungkin ditapis Bilangan keputusan di bawah t_id adalah lebih daripada 2, tetapi keperluan soalan adalah untuk mengambil purata dua keputusan terakhir Selepas purata beberapa keputusan, ia masih sama, jadi tidak perlu memprosesnya lagi. yang dapat memenuhi kehendak soalan.

Purata kumpulan:

SELECT
	t_id,AVG(score)
FROM
	(
	SELECT
		s1.*
	FROM
		student s1
		LEFT JOIN student s2 ON s1.t_id = s2.t_id 
		AND s1.score > s2.score
	GROUP BY
		s1.id 
	HAVING
		COUNT( s1.id )< 2 
	ORDER BY
		s1.t_id,
		s1.score 
	) tt 
GROUP BY
	t_id

Keputusan:

Analisis:

1. Tanya semua rekod t1.skor>t2.skor

SELECT
		s1.*,s2.*
	FROM
		student s1
		LEFT JOIN student s2 ON s1.t_id = s2.t_id 
		AND s1.score > s2.score

2

3. Kumpulan mengikut t_id ambil keputusan setiap subjek dan kemudian ambil purata purata

Soalan 3: Dapatkan kedudukan lima markah teratas untuk setiap subjek (masa tidak dibenarkan)

SELECT
	* 
FROM
	(
	SELECT
		s1.*,
		@rownum := @rownum + 1 AS num_tmp,
		@incrnum :=
	CASE
			
			WHEN @rowtotal = s1.score THEN
			@incrnum 
			WHEN @rowtotal := s1.score THEN
			@rownum 
		END AS rownum 
	FROM
		student s1
		LEFT JOIN student s2 ON s1.t_id = s2.t_id 
		AND s1.score > s2.score,
		( SELECT @rownum := 0, @rowtotal := NULL, @incrnum := 0 ) AS it 
	GROUP BY
		s1.id 
	ORDER BY
		s1.t_id,
		s1.score DESC 
	) tt 
GROUP BY
	t_id,
	score,
	rownum 
HAVING
	COUNT( rownum )< 5

Analisis:

1 Perkenalkan parameter tambahan

SELECT
	s1.*,
	@rownum := @rownum + 1 AS num_tmp,
	@incrnum :=
CASE
		
		WHEN @rowtotal = s1.score THEN
		@incrnum 
		WHEN @rowtotal := s1.score THEN
		@rownum 
	END AS rownum 
FROM
	student s1
	LEFT JOIN student s2 ON s1.t_id = s2.t_id 
	AND s1.score > s2.score,
	( SELECT @rownum := 0, @rowtotal := NULL, @incrnum := 0 ) AS it

2 🎜>

SELECT
		s1.*,
		@rownum := @rownum + 1 AS num_tmp,
		@incrnum :=
	CASE
			
			WHEN @rowtotal = s1.score THEN
			@incrnum 
			WHEN @rowtotal := s1.score THEN
			@rownum 
		END AS rownum 
	FROM
		student s1
		LEFT JOIN student s2 ON s1.t_id = s2.t_id 
		AND s1.score > s2.score,
		( SELECT @rownum := 0, @rowtotal := NULL, @incrnum := 0 ) AS it 
	GROUP BY
		s1.id 
	ORDER BY
		s1.t_id,
		s1.score DESC

3.KUMPULAN OLEH t_id, markah, rownum Kemudian HAVING mengambil 5 unik pertama

Atas ialah kandungan terperinci Contoh analisis kedudukan pengelasan dan pengelompokan TOP N dalam MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!