Comment convertir des lignes en colonnes dans MySQL

Méthode de ligne à colonne MySQL : 1. Utilisez la conversion d'opération "SUM(CASE nom de la table WHEN field name THEN score ELSE 0 END) comme nom de champ" 2. Utilisez "SUM(IF(table name = field name, score, 0)) comme conversion d'opération de nom de champ.

L'environnement d'exploitation de ce tutoriel : système windows10, version mysql8.0.22, ordinateur Dell G3.

Comment convertir des lignes en colonnes dans MySQL

Ligne en colonne

C'est-à-dire que les différents contenus de plusieurs lignes sous la même colonne sont utilisés comme plusieurs champs et le contenu correspondant est affiché.

Créer une instruction de table

DROP TABLE IF EXISTS tb_score;
CREATE TABLE tb_score(
    id INT(11) NOT NULL auto_increment,
    userid VARCHAR(20) NOT NULL COMMENT '用户id',
    subject VARCHAR(20) COMMENT '科目',
    score DOUBLE COMMENT '成绩',
    PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

Insérer des données

INSERT INTO tb_score(userid,subject,score) VALUES ('001','语文',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('001','数学',92);
INSERT INTO tb_score(userid,subject,score) VALUES ('001','英语',80);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','语文',88);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','数学',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','英语',75.5);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','语文',70);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','数学',85);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','英语',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','政治',82);

Interroger le contenu de la table de données (c'est-à-dire le résultat avant la conversion)

SELECT * FROM tb_score

Jetons un coup d'œil au résultat après la conversion :

Cela peut être vu que la ligne change ici, la colonne consiste à sélectionner plusieurs lignes du champ de sujet d'origine comme différentes colonnes dans l'ensemble de résultats, et à les regrouper en fonction de l'ID utilisateur pour afficher le score correspondant.

1. Cas d'utilisation...quand....puis pour convertir des lignes en colonnes

SELECT userid,
SUM(CASE `subject` WHEN '语文' THEN score ELSE 0 END) as '语文',
SUM(CASE `subject` WHEN '数学' THEN score ELSE 0 END) as '数学',
SUM(CASE `subject` WHEN '英语' THEN score ELSE 0 END) as '英语',
SUM(CASE `subject` WHEN '政治' THEN score ELSE 0 END) as '政治' 
FROM tb_score 
GROUP BY userid

2. Utilisez IF() pour convertir des lignes en colonnes :

SELECT userid,
SUM(IF(`subject`='语文',score,0)) as '语文',
SUM(IF(`subject`='数学',score,0)) as '数学',
SUM(IF(`subject`='英语',score,0)) as '英语',
SUM(IF(`subject`='政治',score,0)) as '政治' 
FROM tb_score 
GROUP BY userid

Notes :

(1) SUM ( ) doit pouvoir utiliser GROUP BY pour regrouper selon l'ID utilisateur, car il n'y a qu'un seul enregistrement de subject="langue" correspondant à chaque ID utilisateur, donc la valeur de SUM() est égale à la valeur de score de l'enregistrement correspondant .

S'il y a deux enregistrements avec userid ='001' et subject='中文', alors la valeur de SUM() sera la somme de ces deux enregistrements. De même, la valeur de l'utilisation de Max() sera celle avec. la plus grande valeur parmi ces deux enregistrements. Mais dans des circonstances normales, un utilisateur n'a qu'un seul score correspondant à un sujet, donc des fonctions d'agrégation telles que SUM(), MAX(), MIN() et AVG() peuvent être utilisées pour obtenir l'effet de conversion de ligne.

(2) IF(`subject`='中文',score,0) comme condition, c'est-à-dire l'opération SUM(), MAX(), MIN(), AVG( ), si le score n'a pas de valeur, il la valeur par défaut est 0.

3. Utilisez SUM(IF()) pour générer des colonnes + WITH ROLLUP pour générer des lignes récapitulatives, et utilisez IFNULL pour afficher le titre de la ligne récapitulative sous la forme Total

SELECT IFNULL(userid,'total') AS userid,
SUM(IF(`subject`='语文',score,0)) AS 语文,
SUM(IF(`subject`='数学',score,0)) AS 数学,
SUM(IF(`subject`='英语',score,0)) AS 英语,
SUM(IF(`subject`='政治',score,0)) AS 政治,
SUM(IF(`subject`='total',score,0)) AS total
FROM(
    SELECT userid,IFNULL(`subject`,'total') AS `subject`,SUM(score) AS score
    FROM tb_score
    GROUP BY userid,`subject`
    WITH ROLLUP
    HAVING userid IS NOT NULL
)AS A 
GROUP BY userid
WITH ROLLUP;

Exécutez les résultats :

4. SUM(IF ()) Générez des colonnes + UNION pour générer des lignes récapitulatives et utilisez IFNULL pour afficher les titres des lignes récapitulatives sous la forme Total

SELECT userid,
SUM(IF(`subject`='语文',score,0)) AS 语文,
SUM(IF(`subject`='数学',score,0)) AS 数学,
SUM(IF(`subject`='英语',score,0)) AS 英语,
SUM(IF(`subject`='政治',score,0)) AS 政治,
SUM(score) AS TOTAL 
FROM tb_score
GROUP BY userid
UNION
SELECT 'TOTAL',SUM(IF(`subject`='语文',score,0)) AS 语文,
SUM(IF(`subject`='数学',score,0)) AS 数学,
SUM(IF(`subject`='英语',score,0)) AS 英语,
SUM(IF(`subject`='政治',score,0)) AS 政治,
SUM(score) FROM tb_score

Résultats d'exécution :

5 Utilisez SUM(IF()) pour générer. colonnes, et les résultats générés directement ne sont pas des sous-requêtes de réutilisation

SELECT IFNULL(userid,'TOTAL') AS userid,
SUM(IF(`subject`='语文',score,0)) AS 语文,
SUM(IF(`subject`='数学',score,0)) AS 数学,
SUM(IF(`subject`='英语',score,0)) AS 英语,
SUM(IF(`subject`='政治',score,0)) AS 政治,
SUM(score) AS TOTAL 
FROM tb_score
GROUP BY userid WITH ROLLUP;

Résultats d'exécution :

6 Dynamique, adapté à l'incertitude des colonnes

SET @EE='';
select @EE :=CONCAT(@EE,'sum(if(subject= \'',subject,'\',score,0)) as ',subject, ',') AS aa FROM (SELECT DISTINCT subject FROM tb_score) A ;
SET @QQ = CONCAT('select ifnull(userid,\'TOTAL\')as userid,',@EE,' sum(score) as TOTAL from tb_score group by userid WITH ROLLUP');
-- SELECT @QQ;
PREPARE stmt FROM @QQ;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Résultats d'exécution :

7. : utilisez group_concat()

SELECT userid,GROUP_CONCAT(`subject`,":",score)AS 成绩 FROM tb_score
GROUP BY userid

Résultats d'exécution :

group_concat(), le manuel indique : Cette fonction renvoie un résultat de chaîne avec des valeurs non NULL pour la connexion à partir d'un groupe.

Relativement abstrait et difficile à comprendre. Pour le comprendre de manière simple, c'est en fait comme ceci : group_concat() calculera quelles lignes appartiennent au même groupe et affichera les colonnes appartenant au même groupe. Les colonnes à renvoyer sont déterminées par les paramètres de la fonction (noms de champs). Il doit y avoir une norme de regroupement, qui consiste à regrouper selon la colonne spécifiée par group by.

Conclusion : La fonction group_concat() peut convertir plusieurs lignes appartenant au même groupe en une seule colonne.

Apprentissage recommandé : Tutoriel vidéo mysql

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!