Exemple d'analyse du classement et du regroupement de classification TOP N dans MySQL

Structure du tableau

Le tableau des étudiants est le suivant :

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`)
);

Les données sont les suivantes :

Question 1 : Obtenez les cinq meilleurs classements pour chaque matière (à égalité)

L'égalité autorisée peut existent tels que 4. Si les 5 résultats sont à égalité, les 4 premiers donneront 5 éléments de données, et les 5 premiers donneront également 5 éléments de données.

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 : Lorsque vous prenez le top 4

Analyse :

1. Utilisez sa propre jointure externe gauche pour obtenir tous les ensembles dans lesquels la valeur de gauche est inférieure à la valeur de droite. En prenant t_id=1 comme exemple, 24 a 5 scores supérieurs à lui (74, 64, 54, 44, 34), se classant 6ème, 34 n'a que 4 scores supérieurs à lui, se classant 5ème... .74 Il n'y a personne plus grand que lui et il est le premier.

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

2. Convertissez les règles résumées en SQL et exprimez-les, qui sont regroupées par l'identifiant de chaque élève (s1.id) Avoir des statistiques sur le nombre de valeurs sous cet identifiant qui sont plus grandes que lui (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. Enfin, classez selon t_id et triez par score dans l'ordre inverse.

Question 2 : Obtenez la note moyenne des deux derniers élèves dans chaque matière

Obtenez les deux dernières notes

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

S'ils existent côte à côte, le nombre de résultats filtrés sous le même t_id peut être supérieur à 2, mais la question exige que la moyenne des deux dernières soit prise. Après plusieurs moyennes, elle est toujours la même, il n'est donc plus nécessaire de la traiter, ce qui peut répondre aux exigences de la question. ： Les groupes recherchent la moyenne : 求

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

Résultat :

Analyse :

1 Consultez tous les enregistrements de T1.SCORE et T2.Score

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

2. count prend 2

3. group by t_id prend les sujets respectifs, puis avg prend la moyenne

Question 3 : Obtenez les cinq meilleurs scores pour chaque sujet (le timing n'est pas autorisé)

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

Analyse :

1. Introduisez les paramètres auxiliaires

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. Supprimez le s1.id en double, groupez et triez

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.GROUP BY t_id, score, rownum Ensuite, HAVING prend les 5 premiers non en double

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!